Link to home
Start Free TrialLog in
Avatar of vibale

asked on

Date Query Problem


Im running a query thorugh code but its not working. I think I know why its not working but dont know how to get it to work

Im querying date fields and its not working....however if I make an actual query, and enter the dates in the condition fields in the following format


the query works

When I look at the sql code of the query, the date fields are changed to


The format I have the dates in is dd/mm/ can I get my manual query to work?

Also, I have checked the table design and it is set to short date (dd/mm/yyyy)????
Avatar of rockiroads
Flag of United States of America image

u could try formatting dates on both sides to be yyyymmdd

where format(somedatefield,"yyyymmdd") = format(otherdatefield,"yyyymmdd")
if date field is StartDate, then rewrite it as:

Start_Date: Format(StartDate,"yyyymmdd")

And enter the dates as 20061015

If there is any ambiguity in the date criteria even though entered as dd/mm/yyyy, Access will change it to its default mm/dd/yyyy.  06/07/2008 is interpreted by Access as 2008/06/07 even though the system short date setting is dd/mm/yyyy.  Leave all those problems behind you by using a system short date format that is never ambiguous -

yyyy/mm/dd or dd-mmm-yyyy  -  use what ever date separator suits you.  
Avatar of Moother

fact: access uses dates in calculations in USA format only..., that is to say... mm/dd/yy...

well..., that's not quite true... access works with dates in serial format i.e. the number of days since 01/01/1900 = today....  so access sees 31/12/1900 as 365 - an integer (1900 was NOT a leap year)

so if you use dates in calculation either:
1) convert all to USA format
2) convert the date to an integer with a format(date(),"00000") and then back with another format()
3) use the Dateserial(y,m,d) function

it sucks..., but it's a fact.
I think this is a function of your Language/Locality settings, and I'm not sure that you can change this without changing it across Windows.  If you go into your Control Panel and select "Regional and Language Options" (might be slightly different wording depending upon the OS), you can alter the default short date format.  This will affect the behavior of the operating system as well as other applications (all the Office applications at the very least).  Note, this change will only affect how dates are formatted on the local machine.  Someone using the database on a machine with different locality settings will look different.

When building your queries with code, I would recommend using date serial values rather than formatted dates.  (Look at the dateserial function).
Avatar of vibale


The problem is is that im using a popup calendar, and the 2 dates (startdate and endate) are then stored in comboboxes

I then need to query a table based on those 2 combo' would I achieve this?

The 2 combos are called cbostartdate and cboenddate

Moother:  Access sees #1900-12-31# as 366 not 365 - which is why Format(0,"yyyy-mm-dd") is 1899-12-30.  Lotus Notes designed their calaendar (before Microsoft) and forgot that 1900 was not a leap year.  So in order to be compatible, Bill made the MS calendar day zero = 1899-12-30.  The only thing worse than no information is misinformation;-)
Select myTable.* from myTable where myTable.myDate Between Forms.myFormName.cbostartdate and Forms.myFormName.cboendDate;

BTW what is your short date system setting, what dates are in the combobox, and does the format of the date fields in the combobox match your short date system setting.  If this is all right, then the above query should work - provided there is no ambiguity - as I said in a previous post,
Avatar of vibale


Right, I have a table which has 2 date fields, both set to the following format:


In this table, the dates are entered as 31/08/2006, 07/10/2006 etc etc

The dates in the combo boxes are set as 31/08/2006 etc etc

Still cant get it to work?????

Here is my sql statement

SQL = "SELECT * FROM tbljobs WHERE (tblJobs.fldOrderDate)  Between " & Format(cboStartDate, "dd/mm/yyyy") & " And " & Format(cboEndDate, "dd/mm/yyyy") & ";"

Have also tried

SQL = "SELECT * FROM tbljobs WHERE (tblJobs.fldOrderDate)  Between " & cboStartDate  & " And " & cboEndDate & "#;"


SQL = "SELECT * FROM tbljobs WHERE (tblJobs.fldOrderDate)  Between " & Format(cboStartDate, "ddmmyyyyy") & " And " & Format(cboEndDate, "ddmmyyyy") & ";"


SQL = "SELECT * FROM tbljobs WHERE (tblJobs.fldOrderDate)  Between " & Format(cboStartDate, "yyyymmdd") & " And " & Format(cboEndDate, "yyyymmdd") & ";"


But don't you see - 07/10/2006 - is ambiguous.  In all cases, Access will say that is 10 Jul 2006, even though your format is 'dd/mm/yyyy' - even though you meant 7 Oct 2006.  
Avatar of vibale


so how do I get it to work, ive tried everything suggested?
It's odd behavior.  Rather than trying to format the date in the SQL, use the date serial.  For example, replace "Format(cboStartDate, "dd/mm/yyyy")" from your SQL statement above with Str(dateserial(Year(cboStartDate),Month(cboStartDate),Day(cboStartDate))) to eliminate formatting problems.  Does that provide the proper results?
Avatar of vibale


Do I also have to do that on the value im querying?


Select * from tbljobs where Str(dateserial(Year(tbljobs.fldorderdate),Month(tbljobs.fldorderdate),Day(tbljobs.fldorderdate))) = ............


The formatting in the table should not matter.  Date/times are stored in access as numbers, so they should be able to compare a date field to a serially formatted number.

Obviously I am assuming that the field in question is a date field and not a text field.  If so, the formatting in field's properties only affect the way the data is presented in datasheet view, not the way it is stored in the database.
...if that wasn't clear -- no, you should be able to say Select * from tbljobs where tbljobs.fldorderdate = ...
Avatar of vibale


ok cool ill give it a go and let you know how i get on

Avatar of boxcar7

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of vibale


have tried both and neither worked
Avatar of vibale


Its working!!!

Here is the statement that worked:

SQL = "SELECT * FROM tbljobs WHERE (tblJobs.fldOrderDate)  Between #" & Format(cboStartDate, "mm/dd/yyyy") & "# And #" & Format(cboEndDate, "mm/dd/yyyy") & "#;"
If that worked for u then Im surprised the YYYYMMDD format flags didnt work, Ive always had 100% success with that
Avatar of vibale


Nope that one defintely didnt

Thanks again matey
No probs, very interested in your problem now though as its the 1st time Ive never seen it work.
Avatar of vibale


Its probably down to someting ive done, its all a bit of a mess at the moment!!