Querying with Date Format Problem (Again) (1 Viewer)

I have a (make-table) query with Date parameters. The field is dd/mm/yy which I have converted to yy/mm. I also have two new fields to input the new converted date - StDate and EndDate.

SQL:
PARAMETERS [Enter Start Date yy/mm] Text, [Enter End Date yy/mm] Text;
SELECT DispErrors.*, Format([Enter Start Date yy/mm],"mmmm yyyy") AS StDate, Format([Enter End Date yy/mm],"mmmm yyyy") AS EndDate INTO GetDates
FROM DispErrors
WHERE (((Format([IncDate],"yy/mm")) Between [Enter Start Date yy/mm] And [Enter End Date yy/mm]));

The problem I'm getting is, although the records returned do correspond to the dates I entered, the new fields StDate and EndDate return 2002: eg, if I enter [Start Date] 01/01 and [End Date] 01/08 the text string in the new field returned are January 2002 and August 2002 although the records returned are between January and August 2001.

I did try putting parameters in, but that didn't make any difference.

*** *** *** *** *** *** *** ***
I have just tried taking out the Format([Enter....),"mmmm yyyy" on the new fields and it returned the correct text string, but obviously only as 01/01 and 01/08. So, I thought I'd convert it to "mmmm yyyy" in the report, but the same thing has happened.


Anyone any ideas please?

Ally,

I created a similar query, in A97, and it worked fine, if I entered 01/01 and 01/08 then I got 'January 2001' and 'August 2001' for StDate and EndDate. If I entered 99/01 and 00/04 I got 'January 1999' and 'April 2000', it worked for any combination of parameters and selected the correct records. It worked both with and without the 'Parameter' declarations.

I cannot see any reason for it not working for you. What I'd suggest is try compacting the database, restarting the computer and then trying the query again.

Just one thing to note, it would be best to use 'yyyy/mm' instead of 'yy/mm' as it would allow you to search for dates in the different centuries, StDate and EndDate would not need to have years in the same century to work. If all years are going to be '20##' then the 'yy/mm' will do fine.

Sorry I can't be of more help.

No Go

I can't understand why either - I've done similar things before and there's been no problem. I have compacted several times and re-booted.

I got my colleague to use the same Format ... code in one of his db's and he re-created the exact same problem. It's so frustrating.

The Format function converts to a Variant (String).
That should be the reason why you're not getting the desired results.

Perhaps one of the other members of this forum can give you a in-depth explanation (it's not really my cup of tea...).

Use this statement instead:

SELECT DispErrors.*, Format([Enter Start Date yy/mm],"mmmm yyyy") AS StDate, Format([Enter End Date yy/mm],"mmmm yyyy") AS EndDate INTO GetDates
FROM DispErrors
WHERE CDate(Format([IncDate],"yy/mm"))
Between CDate([Enter Start Date yy/mm])
And CDate([Enter End Date yy/mm]);

I don't know why you're using the PARAMETERS section, it doesn't add anything necesarry. Just drop it.

HTH,

RV

P.S. The CDate converts to a Date Type...

Here's an example you can try in Northwind.

Just copy the following query SQL to a new query, and then
run the query. You'll be prompted to enter a Start mm/yyyy
and then an End mm/yyyy.

When asked for the Start mm/yyyy, enter "01/1996"; then for
End mm/yyyy enter "03/1996" (both without the quotes).

The records returned will be those >= datevalue(01/1996), i.e.,
01/01/96 and < datevalue(04/1996), i.e., 04/01/96. So, the
date range will be 01/01/1996 - 03/31/1996.

Once you've examined it and understand what it's doing, you can then apply it
to your own application.

I used the CDate one - thanks RV. Thanks Raskew too - I looked at the Northwind one, but that was pulling off different dates - I needed it to convert what was entered in the parameters into new fields, so that the time period is entered onto a report.

The CDate one didn't originally work, so I changed it to yyyy/mm in the parameters and it now works. I am a bit puzzled though - I understand that the CDate converts the string to a date field, but on my made table, it still remains a text field?

Out of curiosity I then tried my old query, using yyyy in the parameters and that worked too - (which I should have thought of doing in the first place)!

One more question please...

... I'd understood from a previous post reply from Pat that when we only use month and year, we need to swap the 2 columns so that year is first (major / minor order). Using CDate() does this mean that we can swap them around and use mm/yyyy?