Simple Calculation, but how to manage Date Variable (Range)

But if I compare directly If Me.StartDate > Me.EndDate Then how should Access know 01/06/2012 is Jun. 1st. and not Jan. 6rd. ?
 
Enter the Format property for the control as "Short Date" so that it knows the control is a date.

(It will change automatically to "Short Date" if you enter dd/mm/yyyy and that is your system format.)
 
Enter the Format property for the control as "Short Date" so that it knows the control is a date.

(It will change automatically to "Short Date" if you enter dd/mm/yyyy and that is your system format.)
My system "Short Date" format is dd/mm/yyyy but Access does not change it automaticelly when I put in dd/mm/yyyy


If I use Dlookup (Or DMin, DMAx...) to get data from a date field will I get it as a Date, or do I need to format it ?
 
In all cases where there is an implicit conversion to string, and only those, you have to make an explicit conversion yourself, when your locale setting sets other than US date format. In other words: when the value of a variable or control is written to a string, convert. If such value is assigned or compared to another variable, don't.
 
Now I'm totaly confused with these dates format.

1. If I want a text box to be default for today date what should I put in the default property (Making sure it will be in the dd/mm/yyyy format) ?

2. How do I chack the text box against the day of today ?
 
These date things are making me want to cry :(

when I try to put this in query grid I get data type Mismatch error:
[MyDate] = Format(MyTestDate(),"\#mm\/dd\/yyyy\#")

if I put it like this it's work (give me 372 results)
Format([MyDate],"\#mm\/dd\/yyyy\#") = Format(MyTestDate(),"\#mm\/dd\/yyyy\#")
if I put it like this it's also work (but give me 236 results)
[MyDate] = MyTestDate()

both [MyDate] and MyTestDate() are dates
MyTestDate() read a saved public Date varaible


I have more problems now, when I try to use ConditionalFormat
If I use these it wont work:
[MyDate] <= Format(Date(),"\#mm\/dd\/yyyy\#")
Format([MyDate],"\#mm\/dd\/yyyy\#")<=Format(Date(),"\#mm\/dd\/yyyy\#")

This will work, but how can I be sure it will correctly identify the dates as dd/mm/yyyy:
[MyDate]<=Date()
 
Last edited:
when I try to put this in query grid I get data type Mismatch error:
[MyDate] = Format(MyTestDate(),"\#mm\/dd\/yyyy\#")
That is because Format returns a string and you are comparing with a Date datatype.
if I put it like this it's work (give me 372 results)
Format([MyDate],"\#mm\/dd\/yyyy\#") = Format(MyTestDate(),"\#mm\/dd\/yyyy\#")
if I put it like this it's also work (but give me 236 results)
[MyDate] = MyTestDate()

The diffence in the number of records returned is most likely because some records have a time component included. Converting both to strings eliminates the time component. In the second expression only those already having no time component (zero also represents exactly midnight) will be equal to MyTestDate.

Where Time components are possible in the records the expression is:
Code:
MyDate BETWEEN [MyTestDate] AND DateAdd("s", -1, DateAdd("d", 1, [MyTestDate]))
This expression is preferable to removing the Time from every record because it allows the index on the Date field to be used to select records.

I have more problems now, when I try to use ConditionalFormat
If I use these it wont work:
[MyDate] <= Format(Date(),"\#mm\/dd\/yyyy\#")

Data type mismatch. You are equating a Date to a String.

Format([MyDate],"\#mm\/dd\/yyyy\#")<=Format(Date(),"\#mm\/dd\/yyyy\#")

This one is comparing strings alphanumerically so will be wrong because the order of significance is incorrect. "yyyymmdd" will work but is inefficient.

This will work, but how can I be sure it will correctly identify the dates as dd/mm/yyyy:
[MyDate]<=Date()

The Date datatype itself has no regard for the regional setting. It is the same everywhere. The regional date format setting only changes the way it is displayed, not the way it is compared to another date. The regional setting matters when the date is entered but once in the table there is no confusion.

We must Format dates to mm/dd/yyyy strings when the date is to appear in a string such as an SQL. Otherwise an implict conversion to the regional date format will be inserted. US programmers get away without it and many probably don't even realise their database would fail if the regional settng was changed.
 
Thanks
We must Format dates to mm/dd/yyyy strings when the date is to appear in a string such as an SQL. Otherwise an implict conversion to the regional date format will be inserted. US programmers get away without it and many probably don't even realise their database would fail if the regional settng was changed.
I think this clarify the most of it.

I thought using this style of format return a Date data type:
Format(MyTestDate(),"\#mm\/dd\/yyyy\#")

as for this I'm not sure what to use, as none of the dates include time.
This is used in query grid (In SQL string I only format the second part into the US format).
I suspect formating it in a query grid will change MyTestDate() into a wrong date, keeping [MyDate] unchanged as it come from the table
if I put it like this it's work (give me 372 results)
Format([MyDate],"\#mm\/dd\/yyyy\#") = Format(MyTestDate(),"\#mm\/dd\/yyyy\#")
if I put it like this it's also work (but give me 236 results)
[MyDate] = MyTestDate()
 

Users who are viewing this thread

Back
Top Bottom