Full Version: Cascading Combo box
UtterAccess Forums > Microsoft® Access > Access Forms
Pages: 1, 2
MrSiezen
From what I can remember, SQL and VBA don't always agree on how the date should be noted. I think SQL ALWAYS uses US date format, and VBA uses the regional date format.
So, if you need to convert for SQL like this:
Format(YourDate, "mm-dd-yyyy")
mhearnsww
Hi, thank you for your suggestion. i'm using a dlookup to see if the dates in my start and end date fields on my form are in between the start and end dates in a table.
I've had a go at using the format(YourDate, "mm-dd-yyyy").
Below is some of the formats i've done so far, and they've brought back some wierd answers.
First of all i used;
Me!txtStartOfChargeDate = Format(Me!txtStartOfChargeDate, "dd/mm/yyyy")
Me!txtEndOfChargeDate = Format(Me!txtEndOfChargeDate, "dd/mm/yyyy")
When i typed in '01/04/2003 - 30/09/2003' for my start and end date, the unit cost *which uses the dlookup to view the dates* came back with invalid use of null.
If i typed in '04/01/2003 - 30/09/2003' the dlookup for the start date thinks its US date format and brings back the right unit cost. The standing charge which calculates the datediff between these two dates also brings back the value of 269 days, which is obviously thinking its starting from the 4th jan 2003 instead of 1st april 2003.
The next strange thing is that if i type in '01/04/2004 - 30/09/2004', the unit cost brings back the value of the year '01/04/2003 - 30/09/2003' but the standingcharge now brings back 180 days, which is correct.
Othen changed the startdate format to the following and left the end date as it was.
Me!txtStartOfChargeDate = Format(Me!txtStartOfChargeDate, "mm/dd/yyyy")
I've now typed in '01/04/2003 - 30/09/2003' and because my code runs on the afterupdate of my end date field, the start date then changes format from the UK format to the US format, after i hit enter on the end date field. This then changes the date that i inserted into that field from '01/04/2003' to '04/01/2003', this then brings back the right unit cost, but calculates the standingcharge of 269 days which is wrong.
I once again changed the date from '01/04/2004 - 30/09/2004' and this brings back the right standing charge of 180 days, but brings back the unit cost for the year '01/04/2003 - 30/09/2003' which is wrong.
I just feel like i'm going roung in circles with this one.
Am i using the format(YourDate, "mm-dd-yyyy") correctly?
mhearnsww
Does anyone know why i seem to have this problem?
mhearnsww
Dont worry i managed to fix the problem, took me a while but i got there in the end. Thanx for all the help, much appreciated.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.