473,320 Members | 1,804 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Handeling various data types and white spaces when searching Access DB

68
I have created a search form for my access database. this form submits the table name, the field name and the search text.
After identifying the table to search i then use this clause in my SQL statement to find the results

Expand|Select|Wrap|Line Numbers
  1. WHERE " & myfield & " = '" & mySchTxt & "'"
This works fine when mySchTxt is a single word string. Problem is mySchTxt could sometimes be a string of two or more words or even a number and that's when the errors flow.

I need help in allowing a search for all data types and handling white spaces between submitted strings.
Jun 16 '08 #1
8 1916
DrBunchman
979 Expert 512MB
Hi KingKen,

What errors are you getting?

Is the datatype of myfield always appropriate for the search parameter?

When you pass in mySchTxt and it causes an error have you checked what the value of mySchTxt is at this point (By using a response.write or a msgbox say)? Has it changed from the text that you submitted?

Dr B
Jun 16 '08 #2
KingKen
68
No I have nothing setup to change from text to number. For this it gives me a type mismatch error.

When i search the with white space in the search text i get
"Syntax error in date in query expression 'Model# = 'dell 620''. "
Jun 16 '08 #3
jeffstl
432 Expert 256MB
Ken

If there are multiple strings you want to search on you can't just point to a single string in a query. You would have to divide out your string and make your query more like this

Expand|Select|Wrap|Line Numbers
  1.  
  2. WHERE " & myfield & " = '" & mySchTxt & "' or " & myfield & " = '" & mySchTxt2 & "' or " & myfield & " = '" & mySchTxt3 & "'"
  3.  
  4.  
  5.  
As far as varying datatypes, no data type matters whatsoever except for one. The data type that is in the table behind myfield.

You are going to have to check that datatype if myfield can be different datatypes in the table and print appropriate SQL for each data type. For this you will need to have a select case hard coded with the data types (or something like that)

so....

Expand|Select|Wrap|Line Numbers
  1.  
  2. Select Case MyDataField
  3.  
  4.      Case "ColumnName"
  5.           'for this example I assume ColumnName in a table is a number
  6.           SQL = "place sql here for number datatype"
  7.      Case "AnotherColumnName"
  8.           SQL = "place sql here for text datatype" 
  9.      'and so on..........
  10. End Select
  11.  
  12.  
I see you are essentially constructing a custom query builder that would allow users to search the table for whatever field. These are the kinds of tricks you will need to do to allow that kind of flexibility with a user search from an application side.
Jun 16 '08 #4
KingKen
68
I tried your solution but am still getting data type missmatch error. here is what I have

Expand|Select|Wrap|Line Numbers
  1. SELECT ... WHERE " & myfield & " = "& mySchTxt &""
  2.  
I even tried the Cint() function but nothing budges.

Eventhough the error tells me it is a type mismatch the line points to the following codes

Expand|Select|Wrap|Line Numbers
  1. RecordSet.Open cmdDC, , 3, 3
Does this have anything to do with that error? if so what to do.

One last thing. How do I deal with dates
Jun 17 '08 #5
jeffstl
432 Expert 256MB
I tried your solution but am still getting data type missmatch error. here is what I have

Expand|Select|Wrap|Line Numbers
  1. SELECT ... WHERE " & myfield & " = "& mySchTxt &""
  2.  
I even tried the Cint() function but nothing budges.

Eventhough the error tells me it is a type mismatch the line points to the following codes

Expand|Select|Wrap|Line Numbers
  1. RecordSet.Open cmdDC, , 3, 3
Does this have anything to do with that error? if so what to do.

One last thing. How do I deal with dates
So you got the error, but what is in myfield? What data type is that column? Is it a int or a string?

Dates just use # instead of ' , but sometimes this depends on what database system your using. Most should accept #

So if myfield is a string you use '" & schtxt & "'
if myfield number you use " & schtxt & "
if myfield date you use #" & schtxt & "#

And yes that would be the line that causes the error because that's where your EXECUTING the sql. When you are doing sql = whatever all you are doing is preparing the string
Jun 17 '08 #6
KingKen
68
Ok.
I have implimented the solutions as suggested and still am having trouble.
the string and date data type are functioning just fine but the intiger is still giving errors.

Here is what I am having

Expand|Select|Wrap|Line Numbers
  1. Select Case myTable
  2.      Case "Table1":
  3.      SQL = "SELECT * FROM "& myTable &""
  4.  
  5. If mySchTxt <> "" Then
  6.     If myField="ID" Then
  7.     'CInt ("& mySchTxt &")
  8.     SQL = "SELECT ... WHERE " & myfield & " = " & mySchtxt & ""
  9.         else If myField="DateReported" Then
  10.         SQL = "SELECT ... WHERE " & myfield & " = #"&mySchTxt&"# "
  11.     else
  12.     SQL = "SELECT ... WHERE " & myfield & " = '" & mySchTxt & "'"
  13.     End If
  14.     End If
  15. End If
  16. cmdDC.CommandText = SQL
  17. Set RecordSet = Server.CreateObject("ADODB.Recordset")
  18. RecordSet.Open cmdDC, , 3, 3
It is giving me a data type mis match when searchtxt is intiger.
Jun 17 '08 #7
jeffstl
432 Expert 256MB
Ok.
I have implimented the solutions as suggested and still am having trouble.
the string and date data type are functioning just fine but the intiger is still giving errors.

Here is what I am having

Expand|Select|Wrap|Line Numbers
  1. Select Case myTable
  2.      Case "Table1":
  3.      SQL = "SELECT * FROM "& myTable &""
  4.  
  5. If mySchTxt <> "" Then
  6.     If myField="ID" Then
  7.     'CInt ("& mySchTxt &")
  8.     SQL = "SELECT ... WHERE " & myfield & " = " & mySchtxt & ""
  9.         else If myField="DateReported" Then
  10.         SQL = "SELECT ... WHERE " & myfield & " = #"&mySchTxt&"# "
  11.     else
  12.     SQL = "SELECT ... WHERE " & myfield & " = '" & mySchTxt & "'"
  13.     End If
  14.     End If
  15. End If
  16. cmdDC.CommandText = SQL
  17. Set RecordSet = Server.CreateObject("ADODB.Recordset")
  18. RecordSet.Open cmdDC, , 3, 3
It is giving me a data type mis match when searchtxt is intiger.
Try one more check then to make sure that mySchtxt is actually an integer before you try comparing it to a numeric data type from your database

Expand|Select|Wrap|Line Numbers
  1.  
  2. if IsNumeric(schtxt) then
  3.      'proceed with SQL
  4. else
  5.      'either set schtxt to 0 or report error to user
  6. end if
  7.  
  8.  
Jun 17 '08 #8
KingKen
68
I found the error... I didn't realize that my field name was case sensative so i was using a lowercase where it should have been an uppercase latter.
Thanks for the troubleshooting tip. I'll remember that one.

So problem solved here. Once again thanks you guys.
Jun 18 '08 #9

Sign in to post your reply or Sign up for a free account.

Similar topics

11
by: kenneth | last post by:
I just ran across this. #1 <DBColumn> 1 </DBColumn> #2 <DBColumn> </DBColumn> The data for #1 will be parsed and returned as " 1 ". I get a sequence of...
9
by: Tom Weston | last post by:
Help I have 4 databases containing different data relating to safety, not written by me. It is not possible to merge the databases into one large data base. I would like to create a user interface...
11
by: gopal srinivasan | last post by:
Hi, I have a text like this - "This is a message containing tabs and white spaces" Now this text contains tabs and white spaces. I want remove the tabs and white...
3
by: Prince | last post by:
I have some <RequiredFieldValidator> on my page and everything works fine except that there are lots of white spaces between the web server controls that are being validated. I've set the Display...
4
by: Andreas Prilop | last post by:
How many spaces should be displayed in A <span style="display:none">x</span> B between "A" and "B"? I notice that Mozilla displays one space and Internet Explorer (5 & 6) displays two spaces....
17
by: tommy | last post by:
Hi all, I' m adding strings to some fields in my table via Access. The strings sometimes have trailing spaces and I really need to have it that way, but Access truncates trailing spaces. How can...
12
by: JA | last post by:
Is there a way to remove all the white space in the fields? I have been using Find-and-replace - looking for 2 or 3 or 4 or 10 spaces and replacing them with none. I don't want to replace single...
0
by: gomzi | last post by:
hi, I am currently checking for white spaces in a texbox using "\s" in regex . But even when the texbox contains spaces after a word or between two words, say firstname and lastname, the regex...
5
by: MadDiver | last post by:
Guys, I need to create an application that handles several product types/ categories. Each product type can have totally different fields to describe it. For instance a car would have Year,...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.