Bug in Access perhaps? Date Expressions

[sorcerer11]

I have a weird problem with selecting between two dates if the dates have been worked out using an expression.

For example here is my expression.

Real Date: IIf([ShiftPat]="Night",[CDate]-1,[CDate])

and here is my selection critera

Between [Enter First Date?] And [Enter Last Date?]

This doesn't work because it gives me an incorrect selection of dates, it basically treats it like a number. Any ideas on how I can get Access to recgonize this as a date. I have tried various conversion statements and I haven't had much luck.

Thanks

Sorcerer

 

[DanChard]

As long as you know how the user is going to enter the date (ie, the format of the date - "07-jul-03&quot then you can use the format function around the [Enter First Date] and [Enter Last Date] parameters:

Between format([Enter First Date?],'dd-mmm-yy') And format([Enter Last Date?],'dd-mmm-yy')

This would force Access to treat the input as a date but if the input it gets doesn't convert to a date using the fomrat provided then it will throw an error.

There's a good section in the Access help on creating date formats - I think you just have to search on Date/Time.

I hope this is of some use to you.

 

[sorcerer11]

Tried that but it didn't seem to make a difference, I tried various forms of format and I still seem to get the incorrect selection of dates.

For example if I ask for a date range between 01/06/2003 and 05/06/2003, I will get something like this

01/06/2003
01/05/2003
05/03/2003
04/04/2004

Basically it will give me other days that start with the same number. Even if I type it in manually and use # it seems to do the same thing.

 

[DanChard]

Is there a chance it could be mis-reading the dates and interpretting them as mm/dd/yyyy rather than dd/mm/yyyy or vice versa? Most date problems that I run into are usually something to do with this...

 

[sorcerer11]

I don't believe it has anything to do with dates because I tried it out and it gave me the same result.

This criteria works

Between #01/06/2003# and #06/06/2003#

but this does not

Between [Date 1] and [Date 2]

But I need to use the second expression so that the user can enter the date's that they want to use.

What I need is something like this

Betweek #[Date1]# and #[Date2]#

but unfortunately this is an illegal expression.

 

[sorcerer11]

Sorry last expression should read

Between #[Date1]# and #[Date2]#

 

[sorcerer11]

Sorted it

This seemed to work

Between CDate([Date1]) and CDate([Date2])

Thanks for your help.

 

[scriverb]

I really suggest that you use the DateAdd function when modifying a date. Sometimes what you have done here works other times it doesn't. Recommend the following:

Date: IIf([ShiftPat]="Night",DateAdd("d",-1,[CDate]),[CDate])

With an expression prompt like this the Format function does absolutely no good as it only displays what is entered in the described format. But, that would be too late as the OK button closes the window so only the keyed in characters are what the user sees. There is no InputMask function which is what would be nice here.
Between #[Enter First Date?]# And #[Enter Last Date?]#

You see what is returned from the prompted expression is a string of characters that ACCESS does not recognize as a date value until you either use the CDate function or bracket it with # signs. Both convert the string to a date value.

Post back with questions.


Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???