Syntax problems

[asdis]

Hi,

I am having problems with syntax. So, I have a table (TableName) with four fields; FieldName, FieldName2, FieldName3 and FieldName4. FieldName and FieldName2 make up the primary key. I want to find the last two fields with some given conditions on the primary key.

"SELECT" & FieldName3 & "AND" & FieldName4 & _
"FROM" & TableName & _
"WHERE" &FieldName& "#" & CStr(Year(jour)) & "/" & CStr(Month(jour)) & "/" & CStr(Day(jour)) & "#" & _
"AND" &FieldName2 & "#" & CStr(Hour(heure)) & ":" & CStr(Minute(heure)) & "#"

If anyone can explain why this is not working it would be great! Thank you

Asdis

 

[SJMcAbney]

You need to put spaces into your statement, otherwise you end up with:

SELECTMyFieldFROMMyTableWHEREThis=ThatANDThat=This;

Also, you separate the fields to select by a comma and do not use the AND operator. Also there is an easier way to build a string - by using the DateSerial() function. However you are just breaking down the jour variable; you can use this simply as the date.

You also need equals sign in your criteria.


"SELECT " & FieldName3 & "," & FieldName4 & _
" FROM " & TableName & _
" WHERE " & FieldName& " = #" & jour & "# " & _
" AND " & FieldName2 & " = #" & heure & "#;"

The only other thing I'd comment upon is your use of one field for a date and one field for a time. You should only need one field to store this containing both the date and the time.




Stewart J. McAbney | Talk History

 

[RoyVidar]

In addition to SJMcAbney's advice, you may have to format the date and time to an unambigous date format, if your date settings differs from US format.

See for instance International Dates in Access for an explanation and format strings for dates, and Return Dates in US #mm/dd/yyyy# format for sample format strings for both date and time.

Roy-Vidar

 

[PHV]

My guess:
"SELECT FieldName3,FieldName4" & _
" FROM TableName" & _
" WHERE FieldName=#" & Format(jour, "m/dd/yyyy") & "#" & _
" AND FieldName2=#" & Format(heure, "h:nn") & "#"

You may have problem if seconds are stored in FieldName2
Bonne chance.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[asdis]

Thank you all very much for the replies. They have been very helpful... I am now trying to fix my code but unfortunately it isn't error free yet