date format problem

[Guest]

Hi

I am using access 2000, windows xp

have a form which has a text box (text35) which contains a date with short
date format. regional setting is dd/mm/yyyy.

i have a query in the form coding as follows:

SELECT * FROM AccountRecs WHERE GLCode = " & Nz(Me.Combo0, 0) & " AND [date]
= " & Format([Text35], "\#mm\/dd\/yyyy\#")

there are 3 records in the AccountRecs table with dates 09/07/2007,
10/07/2007 and 11/07/2007.
when text35 contains any of these dates the query has a record count of 3,
whereas it should only have 1

when i remove the 'GLCode = " & Nz(Me.Combo0, 0) & " AND' part it seems to
work, but i need to match both criteria

any thoughts much appreciated.

thanks

David
David Benjamin

 

[Rick Brandt]

Hi

I am using access 2000, windows xp

have a form which has a text box (text35) which contains a date with
short date format. regional setting is dd/mm/yyyy.

i have a query in the form coding as follows:

SELECT * FROM AccountRecs WHERE GLCode = " & Nz(Me.Combo0, 0) & " AND
[date] = " & Format([Text35], "\#mm\/dd\/yyyy\#")

there are 3 records in the AccountRecs table with dates 09/07/2007,
10/07/2007 and 11/07/2007.
when text35 contains any of these dates the query has a record count
of 3, whereas it should only have 1

when i remove the 'GLCode = " & Nz(Me.Combo0, 0) & " AND' part it
seems to work, but i need to match both criteria

any thoughts much appreciated.

thanks

David
David Benjamin

Date literals in Access queries do not honor your regional settings. They must
be a non-ambiguous format or US format m/d/y. ISO format yyyy-mm-dd is the best
choice in my opinion.

 

[Douglas J. Steele]

Hi

I am using access 2000, windows xp

have a form which has a text box (text35) which contains a date with
short date format. regional setting is dd/mm/yyyy.

i have a query in the form coding as follows:

SELECT * FROM AccountRecs WHERE GLCode = " & Nz(Me.Combo0, 0) & " AND
[date] = " & Format([Text35], "\#mm\/dd\/yyyy\#")

there are 3 records in the AccountRecs table with dates 09/07/2007,
10/07/2007 and 11/07/2007.
when text35 contains any of these dates the query has a record count
of 3, whereas it should only have 1

when i remove the 'GLCode = " & Nz(Me.Combo0, 0) & " AND' part it
seems to work, but i need to match both criteria

any thoughts much appreciated.

Date literals in Access queries do not honor your regional settings. They
must be a non-ambiguous format or US format m/d/y. ISO format yyyy-mm-dd
is the best choice in my opinion.

While it's true that Access doesn't respect regional settings in queries,
David's correctly formatting the date. However, it's probable that Access
isn't correctly recognizing the date in the text box.

Try

SELECT * FROM AccountRecs WHERE GLCode = " & Nz(Me.Combo0, 0) & " AND
[date] = " & Format(CDate([Text35]), "\#mm\/dd\/yyyy\#")

(CDate does respect regional settings)

 

[Guest]

Hi Doug

thanks for suggestion but didn't work. when i try to match just the form
date with the date in table it finds the right record/number of records and
when i try to match just the combo value with the field in the table it finds
the right record/number of records, but trying to match both does not. at
present the table (AccountRecs) has 3 records, all with the same GLCode but
all with different dates. so at present trying to match both the GLCode and
Date from the table to the values from the form retrieves 3 records, which it
should not.
i have done similar things many times before with correct result so i'm at a
loss as to why this is not.

any other suggestions???

thanks

David
--
David Benjamin

Hi

I am using access 2000, windows xp

have a form which has a text box (text35) which contains a date with
short date format. regional setting is dd/mm/yyyy.

i have a query in the form coding as follows:

SELECT * FROM AccountRecs WHERE GLCode = " & Nz(Me.Combo0, 0) & " AND
[date] = " & Format([Text35], "\#mm\/dd\/yyyy\#")

there are 3 records in the AccountRecs table with dates 09/07/2007,
10/07/2007 and 11/07/2007.
when text35 contains any of these dates the query has a record count
of 3, whereas it should only have 1

when i remove the 'GLCode = " & Nz(Me.Combo0, 0) & " AND' part it
seems to work, but i need to match both criteria

any thoughts much appreciated.

Date literals in Access queries do not honor your regional settings. They
must be a non-ambiguous format or US format m/d/y. ISO format yyyy-mm-dd
is the best choice in my opinion.

While it's true that Access doesn't respect regional settings in queries,
David's correctly formatting the date. However, it's probable that Access
isn't correctly recognizing the date in the text box.

Try

SELECT * FROM AccountRecs WHERE GLCode = " & Nz(Me.Combo0, 0) & " AND
[date] = " & Format(CDate([Text35]), "\#mm\/dd\/yyyy\#")

(CDate does respect regional settings)