Problem in query...

[colemauro]

I need to have this query that gives me some info

Select Client From Agenda Where date =14/08/2008 AND hour =('09:30');

I have tried all sort of combinations and this is the one that doesn'te give an error..but it only returns a blank result, so something is still wrong..

Yes i have a record with this hour and date..

Can someone help me out on this?

 

[dkinley]

The problem is probably in your formatting ... dates are to be surrounded by # date # ... do a search this aspect and you will turn up tons of results.

-dK

 

[colemauro]

I have tried what you said with the # # but no good.

I use data as data type, and as string to see if it helped, but still no good.

I resourche a lot before i ask you guys this, but i must be doing something wrong.

I will continue the research.

Thanks

 

[boblarson]

Are date and hour separate fields named that in your database?

 

[colemauro]

Yes they are..

This is for an agenda..like outlook calendar..

A Agenda with the 7 days of the week and one each day x hours seperated by 30 min

09:00
09:30
...

So to make an appointment i have one for the date and other for the hour.

 

[boblarson]

1. change the field names because using Access Reserved words (see here for a list:
http://www.allenbrowne.com/AppIssueBadWord.html
will only cause you pain and suffering (and us too as we try to help you "band-aid" a solution).

2. Is the hour stored as text?

 

[colemauro]

1. The nome for the fields are in Portuguese while i use Access 2003 in English so no problem there.

2. As any programmer i tried all i could think of..as for the date and hour currently i left them as String.

 

[boblarson]

Perhaps you should post your db here. See here for how:
http://www.access-programmers.co.uk/forums/showthread.php?t=140587

 

[raskew]

Hi -

x = #8/14/08 09:30:00#
? x
8/14/2008 9:30:00 AM
? cdbl(x)
39674.3958333333
strSQL = "Select Client From Agenda Where xdate = #8/14/08 09:30:00#;"
? strSQL
Select Client From Agenda Where xdate = #8/14/08 09:30:00#;

strSQL = "Select Client From Agenda Where xdate = #" & x & "#;"
? strSQL
Select Client From Agenda Where xdate = #8/14/2008 9:30:00 AM#;

As was previously explained, dates/times are surrounded with # #.
The cdbl(x) shown above is how Access stores dates/times.

See this MSKB article for a further explanation of how
Access stores dates/times: http://support.microsoft.com/kb/q130514/

Since both Date and Time are reserved words (names of functions) thus shouldn't be used as field names. It confuses the hell out of the system.

Bob

 

[JGT]

raskew is right - in sql you always use the US representation for the date, sql will not recognize the dd/mm/yyyy format (the same way you use 5.6 and not 5,6 in sql)

 

[colemauro]

I will look into what you said.

Thanks a lot guys.