How should I be using Numbers in SQL?

[JayCR]

I have found that (yet again) I am having problems with SQL, I dont know if I am being daft or otherwise.

 
Dim sqlSearcher as String
Dim FormUserID as String
Dim FormNumber as Integer

sqlSearcher = "Select * FROM tblUserForms"
sqlSearcher = sqlSearcher & " " & "WHERE [UserID_] = '" & FormUserID & "' and [FormIDno_] = '" & FormNumber & "'"

msgbox sqlSearcher ' this then dispays "sqlSearcher = Select * FROM tblUserForms WHERE [UserID_] = 'jfreeman' and [FormIDno_] = '7'

Now the error I get is "Data Type Mismatch in Criteria expression." after running the contents of the msgbox (just above) through the SQL Query generator in access. this returns the same error.

I ran the following SQL query through access and it worked fine

Select * FROM tblUserForms WHERE [UserID_] = 'jfreeman' and [FormIDno_] = 7

I have tried removing the ticks after the WHERE [UserID_] = '" & FormNumber & "'" and this makes no difference, I tried removing all the quotes and juggling them, to no avail.

Please someone tell me this is a complicated problem as I am beginning to feel a bit of a fool for regularly asking some seemingly silly questions...

Thanks

J

[GaryMazzone]

When you query a database any thing that is text (in SQL Server, Oracle Varchar) you put the thing between single qoute marks

Select * from Users where FirstName = 'Sam'

When you query with dates it is the same (MSAccess usine # instead of ')

For number you do not use qoutes
Select * from Users Where UserPK = 9

[Besoup]

no need for the single quotes around the integer.

[si_the_geek]

Indeed, this is how it should be:

... & "' and [FormIDno_] = " & FormNumber & ""

For a fuller explanation and examples of delimiters to use around values within SQL statements, see the article How do I use values (numbers, strings, dates) in SQL statements? from the "SQL" section of our Database Development FAQs/Tutorials (at the top of this forum)

[JayCR]

Annoyingly I did try that, and it is like that now, but for some reason it wasnt working, and now it is, I guess thats because I was still in debug mode and the sql hadn't been regenerated!

also, I tried to put "Debug.Print sqlSearcher" before, and after the highlighted erroneous line, however it didnt work, the intermediate window is open but that it didnt show the statement and values. Now I did enter into the immediate window "?sqlSearcher" and that returned my sql statement as its being processed, am I doing something particularly wrong?

[si_the_geek]

Annoyingly I did try that, and it is like that now, but for some reason it wasnt working, and now it is, I guess thats because I was still in debug mode and the sql hadn't been regenerated!

I presume you changed the line after it had run - in which case the changes wouldn't have any effect.

also, I tried to put "Debug.Print sqlSearcher" before, and after the highlighted erroneous line, however it didnt work, the intermediate window is open but that it didnt show the statement and values. Now I did enter into the immediate window "?sqlSearcher" and that returned my sql statement as its being processed, am I doing something particularly wrong?

It should be after you have built it (so after the line with "WHERE" in it), and before you run it (so that it is printed even if there is an error).

Then it should work fine.

[JayCR]

Thanks Si, again I think I might have put that in during debug mode... well hopefully that is the last of the silly questions...

I am wanting to advance further with this, and am wondering if you can suggest any good read's for learning / troubleshooting various SQL and other common problems, most of which I have posted about? I know that there are the FAQ's but do you know of any good books/ebooks/etc?

Thanks

J

[si_the_geek]

For the kind of things you have been doing so far, the FAQs should be enough... they contain info about troubleshooting (in the SQL section, and the 'what does this error mean?' article), as well as tutorials for writing SQL, and links to the documentation for each database system (which you probably don't need yet, as it only matters for more advanced topics).

Several people also recommend www.w3schools.com/sql/

[JayCR]

Ok Thanks Si, and thank you everyone else!