help in date

[anil]

hi all
I am using the following code(partof) to select date from the forms:

SQLStr = "Select * from tbllog where [tbllog].[Adate]=#" &
Format([Forms]!frmSamplingSchedule!SelectADate, "dd/mm/yyyy") & " # "

rst.Open SQLStr

If rst.RecordCount = 0 Then
rst.Close
MsgBox "There are No Records in the table for Date = " &
Format([Forms]!frmSamplingSchedule!SelectADate, "dd/MM/yyyy") & " "
Exit Sub
Else
MsgBox "Date " &
Format([Forms]!frmSamplingSchedule!SelectADate, "dd/mm/yyyy") & "
found"
Debug.Print rst.RecordCount
rst.MoveFirst

Now the problem is I am not able to select all dates .Although I have
to select weekending date but I am testing for all dates now.So I
tested for
21/4/2006,28/4/2006,5/5/2006,12/5/2006,19/5/2006,26/5/2006,2/6/2006,9/6/2006,16/7/2006.

when I select 12/5/2006,2/6/2006,9/6/2006,I am not getting any results
and getting recordcount =0,although there are records in the table.For
all other records I am getting results.Is there any wrong in code or I
am working wrong.Please help me It's driving me crazy.
thanks
anil

 

[Dirk Goldgar]

hi all
I am using the following code(partof) to select date from the forms:

SQLStr = "Select * from tbllog where [tbllog].[Adate]=#" &
Format([Forms]!frmSamplingSchedule!SelectADate, "dd/mm/yyyy") & " # "

rst.Open SQLStr

If rst.RecordCount = 0 Then
rst.Close
MsgBox "There are No Records in the table for Date = " &
Format([Forms]!frmSamplingSchedule!SelectADate, "dd/MM/yyyy") & " "
Exit Sub
Else
MsgBox "Date " &
Format([Forms]!frmSamplingSchedule!SelectADate, "dd/mm/yyyy") & "
found"
Debug.Print rst.RecordCount
rst.MoveFirst

Now the problem is I am not able to select all dates .Although I have
to select weekending date but I am testing for all dates now.So I
tested for
21/4/2006,28/4/2006,5/5/2006,12/5/2006,19/5/2006,26/5/2006,2/6/2006,9/6/
2006,16/7/2006.

when I select 12/5/2006,2/6/2006,9/6/2006,I am not getting any results
and getting recordcount =0,although there are records in the table.For
all other records I am getting results.Is there any wrong in code or I
am working wrong.Please help me It's driving me crazy.
thanks
anil

In Jet SQL, if a date literal can be interpreted as being in US-standard
month/day/year format, it will be. You should format your date literals
either in that format, or in ISO standard YYYY-MM-DD format.

Try this:

SQLStr = _
Select * from tbllog where [tbllog].[Adate]=#" & _
Format([Forms]!frmSamplingSchedule!SelectADate, _
mm/dd/yyyy") & _
" # "

 

[anil]

hi dirk
thanks ,that solved the problem.It is now selecting the date,but not
able to append or update the tables.I am in Australia and have changed
reigonal setting to Australia(dd/mm/yyyy).So please suggest me should I
change my original setting to US or work in Access in mm/dd/yyyy
settings.
thanks
anil

 

[Dirk Goldgar]

hi dirk
thanks ,that solved the problem.It is now selecting the date,but not
able to append or update the tables.I am in Australia and have changed
reigonal setting to Australia(dd/mm/yyyy).So please suggest me should
I change my original setting to US or work in Access in mm/dd/yyyy
settings.

Anil -

There's no need to change your regional date setting -- Australia is
fine. It's only when you specify a date literal in a SQL string that
you have to contend with Jet's assumptions.

I don't know what sort of problem you are having with updating tables.
Where and when is this happening? Is there any reason to believe it's
related to the dates? I don't see why it would be.

 

[anil]

hi dirk
I am updating the table where the date is 12/05/2006(like that). using
date as condition or parameter which I select from form.
rest now it seems fine
thanks for ur advice.
anil