469,315 Members | 1,634 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,315 developers. It's quick & easy.

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 1722
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

Post your reply

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

Similar topics

11 posts views Thread by kenneth | last post: by
11 posts views Thread by gopal srinivasan | last post: by
3 posts views Thread by Prince | last post: by
4 posts views Thread by Andreas Prilop | last post: by
12 posts views Thread by JA | last post: by
5 posts views Thread by MadDiver | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by harlem98 | last post: by
1 post views Thread by Geralt96 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.