Bizarre Select Results

  • Thread starter Thread starter Jim Chapman
  • Start date Start date
J

Jim Chapman

Hi Group,
I am using Access97 on WinXP platform. I have a form frmAttendances on
which there is a control fldDate. Basically I display a list of people and
if a date is selected in fldDate by clicking on a records date field I
insert the fDate value into that record. This all works but I found that it
was posssible to replicate data so I wrote a short routine to open a
recordset to check for previous entries for fldDate. It all worked fine!
BUT when I tested it over a range of months I must have used all odd months
'cause it doesnt work for any of the even ones. My select statement for
opening the recordset is.
StrSQL = "SELECT * From MeetingAttendances " & _
"WHERE MeetingDate = # " & [Forms]![frmAttendances]![fldDate] & _
" # ORDER BY MemberID ;"
Set rst = dbs.OpenRecordset(StrSQL)
I have checked the data and I can find nothing to account for the select
failing to pick up the records. Any thoughts would be greatly appreciated.

Jim
 
I guess it depends on your default date format whether you
have problems or not. However, please note that #-
delimited date must be in the US-format mm/dd/yyyy
regardless of the regional settings. If you default date
format is different from mm/dd/yyyy, you need to re-format
it to the above before you can use it as #-delimited
explicit date value in the SQL String.

HTH
Van T. Dinh
MVP (Access)
 
Hello Van,
Grrr it is so simple once it is pointed out. Of course, I am using
English/Australian date format dd/mm/yy. The ones that worked just happened
to be 03/03/04, 05/05/04 and 07/07/04 ie the first Wednesday of the month.
Thank you so much for your speedy help.
Kindest Regards
Jim
Van T. Dinh said:
I guess it depends on your default date format whether you
have problems or not. However, please note that #-
delimited date must be in the US-format mm/dd/yyyy
regardless of the regional settings. If you default date
format is different from mm/dd/yyyy, you need to re-format
it to the above before you can use it as #-delimited
explicit date value in the SQL String.

HTH
Van T. Dinh
MVP (Access)


-----Original Message-----
Hi Group,
I am using Access97 on WinXP platform. I have a form frmAttendances on
which there is a control fldDate. Basically I display a list of people and
if a date is selected in fldDate by clicking on a records date field I
insert the fDate value into that record. This all works but I found that it
was posssible to replicate data so I wrote a short routine to open a
recordset to check for previous entries for fldDate. It all worked fine!
BUT when I tested it over a range of months I must have used all odd months
'cause it doesnt work for any of the even ones. My select statement for
opening the recordset is.
StrSQL = "SELECT * From MeetingAttendances " & _
"WHERE MeetingDate = # " & [Forms]![frmAttendances]! [fldDate] & _
" # ORDER BY MemberID ;"
Set rst = dbs.OpenRecordset(StrSQL)
I have checked the data and I can find nothing to account for the select
failing to pick up the records. Any thoughts would be greatly appreciated.

Jim


.
Click to expand...
Click to expand...
 
Back
Top