By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,660 Members | 1,579 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,660 IT Pros & Developers. It's quick & easy.

DLookup Date retrieval problem when changing month in a loop, VBA Access 2003

P: n/a
Hi all

the following code works perfectly well. Basically it populates a
series of check boxes on my form, depending on whether dlookup finds an
associated record.

The problem i have is that when the date (or xDate) goes over to the
next month, dlookup returns NULL even though though there is a record.

For example if CALWEEKSTART is 26/11/06 then the loop will check for
records for the
26th then the 27th then 28th then 29th then 30th then 01st then 02nd.

Now if there are records then it will return the AvailabilityID into
the EARLY variable. So If the EARLY variable IS NOT NULL then it will
put a tick in the correct check box, if it returns a NULL value then it
will remove the tick box.

Even if there are records on the 1st and 2nd it wont return the
availabilityID of it, it returns a NULL. If i start the loop from the
1st of the month (so checking the 1st, 2nd, 3rd, 4th, 5th, 6th, 7th) it
works fine.

Its a problem with the DLookup line, ive even surrounded the date in #
's (Date is stored in short format and there are no times in the field)

This is very frustrating can anyone help.

Access 2003

Chris Boyle
NHS Trust

---------------------

Dim xDate as Date

'Loop to populate availability table
x = 0
Do While x < 7
xDate = CalWeekStart + x

early = DLookup("[AvailabilityID]", "[Availability]", "[candidateid] =
" & Forms!availability!CandidateID & " and [Availdate] = #" & xDate &
"# and [shifttimeid] = " & EarlyID)

'MsgBox ("[candidateid] = " & Forms!availability!CandidateID & "
and [Availdate] = #" & xDate & "# and [shifttimeid] = " & EarlyID)
If IsNull(early) = False And x = 0 Then
Forms!availability!subAvailabilityChild!chkME = True
If IsNull(early) = True And x = 0 Then
Forms!availability!subAvailabilityChild!chkME = False
If IsNull(early) = False And x = 1 Then
Forms!availability!subAvailabilityChild!chkTE = True
If IsNull(early) = True And x = 1 Then
Forms!availability!subAvailabilityChild!chkTE = False
If IsNull(early) = False And x = 2 Then
Forms!availability!subAvailabilityChild!chkWE = True
If IsNull(early) = True And x = 2 Then
Forms!availability!subAvailabilityChild!chkWE = False
If IsNull(early) = False And x = 3 Then
Forms!availability!subAvailabilityChild!chkThE = True
If IsNull(early) = True And x = 3 Then
Forms!availability!subAvailabilityChild!chkThE = False
If IsNull(early) = False And x = 4 Then
Forms!availability!subAvailabilityChild!chkFE = True
If IsNull(early) = True And x = 4 Then
Forms!availability!subAvailabilityChild!chkFE = False
If IsNull(early) = False And x = 5 Then
Forms!availability!subAvailabilityChild!chkSaE = True
If IsNull(early) = True And x = 5 Then
Forms!availability!subAvailabilityChild!chkSaE = False
If IsNull(early) = False And x = 6 Then
Forms!availability!subAvailabilityChild!chkSuE = True
If IsNull(early) = True And x = 6 Then
Forms!availability!subAvailabilityChild!chkSuE = False

x = x + 1

Loop

Nov 27 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Your examples indicate you live in a d/m/y country.
The SQL statements in JET (and therefore the arguments for DLookup()) need
to be in US format.

Try:
early = DLookup("[AvailabilityID]", "[Availability]", _
"([candidateid] =" & Forms!availability!CandidateID & _
") and ([Availdate] = #" & Format(xDate, "mm\/dd\/yyyy") & _
"#) and [shifttimeid] = " & EarlyID)

For more info on how to avoid the 3 cases where JET is likely to
misunderstand your dates, see:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<bo*****@gmail.comwrote in message
news:11*********************@h54g2000cwb.googlegro ups.com...
Hi all

the following code works perfectly well. Basically it populates a
series of check boxes on my form, depending on whether dlookup finds an
associated record.

The problem i have is that when the date (or xDate) goes over to the
next month, dlookup returns NULL even though though there is a record.

For example if CALWEEKSTART is 26/11/06 then the loop will check for
records for the
26th then the 27th then 28th then 29th then 30th then 01st then 02nd.

Now if there are records then it will return the AvailabilityID into
the EARLY variable. So If the EARLY variable IS NOT NULL then it will
put a tick in the correct check box, if it returns a NULL value then it
will remove the tick box.

Even if there are records on the 1st and 2nd it wont return the
availabilityID of it, it returns a NULL. If i start the loop from the
1st of the month (so checking the 1st, 2nd, 3rd, 4th, 5th, 6th, 7th) it
works fine.

Its a problem with the DLookup line, ive even surrounded the date in #
's (Date is stored in short format and there are no times in the field)

This is very frustrating can anyone help.

Access 2003

Chris Boyle
NHS Trust

---------------------

Dim xDate as Date

'Loop to populate availability table
x = 0
Do While x < 7
xDate = CalWeekStart + x

early = DLookup("[AvailabilityID]", "[Availability]", "[candidateid] =
" & Forms!availability!CandidateID & " and [Availdate] = #" & xDate &
"# and [shifttimeid] = " & EarlyID)

'MsgBox ("[candidateid] = " & Forms!availability!CandidateID & "
and [Availdate] = #" & xDate & "# and [shifttimeid] = " & EarlyID)
If IsNull(early) = False And x = 0 Then
Forms!availability!subAvailabilityChild!chkME = True
If IsNull(early) = True And x = 0 Then
Forms!availability!subAvailabilityChild!chkME = False
If IsNull(early) = False And x = 1 Then
Forms!availability!subAvailabilityChild!chkTE = True
If IsNull(early) = True And x = 1 Then
Forms!availability!subAvailabilityChild!chkTE = False
If IsNull(early) = False And x = 2 Then
Forms!availability!subAvailabilityChild!chkWE = True
If IsNull(early) = True And x = 2 Then
Forms!availability!subAvailabilityChild!chkWE = False
If IsNull(early) = False And x = 3 Then
Forms!availability!subAvailabilityChild!chkThE = True
If IsNull(early) = True And x = 3 Then
Forms!availability!subAvailabilityChild!chkThE = False
If IsNull(early) = False And x = 4 Then
Forms!availability!subAvailabilityChild!chkFE = True
If IsNull(early) = True And x = 4 Then
Forms!availability!subAvailabilityChild!chkFE = False
If IsNull(early) = False And x = 5 Then
Forms!availability!subAvailabilityChild!chkSaE = True
If IsNull(early) = True And x = 5 Then
Forms!availability!subAvailabilityChild!chkSaE = False
If IsNull(early) = False And x = 6 Then
Forms!availability!subAvailabilityChild!chkSuE = True
If IsNull(early) = True And x = 6 Then
Forms!availability!subAvailabilityChild!chkSuE = False

x = x + 1

Loop

Nov 27 '06 #2

P: n/a
You are an absolute genius Mr Browne

My many thanks

Chris Boyle
Allen Browne wrote:
Your examples indicate you live in a d/m/y country.
The SQL statements in JET (and therefore the arguments for DLookup()) need
to be in US format.

Try:
early = DLookup("[AvailabilityID]", "[Availability]", _
"([candidateid] =" & Forms!availability!CandidateID & _
") and ([Availdate] = #" & Format(xDate, "mm\/dd\/yyyy") & _
"#) and [shifttimeid] = " & EarlyID)

For more info on how to avoid the 3 cases where JET is likely to
misunderstand your dates, see:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<bo*****@gmail.comwrote in message
news:11*********************@h54g2000cwb.googlegro ups.com...
Hi all

the following code works perfectly well. Basically it populates a
series of check boxes on my form, depending on whether dlookup finds an
associated record.

The problem i have is that when the date (or xDate) goes over to the
next month, dlookup returns NULL even though though there is a record.

For example if CALWEEKSTART is 26/11/06 then the loop will check for
records for the
26th then the 27th then 28th then 29th then 30th then 01st then 02nd.

Now if there are records then it will return the AvailabilityID into
the EARLY variable. So If the EARLY variable IS NOT NULL then it will
put a tick in the correct check box, if it returns a NULL value then it
will remove the tick box.

Even if there are records on the 1st and 2nd it wont return the
availabilityID of it, it returns a NULL. If i start the loop from the
1st of the month (so checking the 1st, 2nd, 3rd, 4th, 5th, 6th, 7th) it
works fine.

Its a problem with the DLookup line, ive even surrounded the date in #
's (Date is stored in short format and there are no times in the field)

This is very frustrating can anyone help.

Access 2003

Chris Boyle
NHS Trust

---------------------

Dim xDate as Date

'Loop to populate availability table
x = 0
Do While x < 7
xDate = CalWeekStart + x

early = DLookup("[AvailabilityID]", "[Availability]", "[candidateid] =
" & Forms!availability!CandidateID & " and [Availdate] = #" & xDate &
"# and [shifttimeid] = " & EarlyID)

'MsgBox ("[candidateid] = " & Forms!availability!CandidateID & "
and [Availdate] = #" & xDate & "# and [shifttimeid] = " & EarlyID)
If IsNull(early) = False And x = 0 Then
Forms!availability!subAvailabilityChild!chkME = True
If IsNull(early) = True And x = 0 Then
Forms!availability!subAvailabilityChild!chkME = False
If IsNull(early) = False And x = 1 Then
Forms!availability!subAvailabilityChild!chkTE = True
If IsNull(early) = True And x = 1 Then
Forms!availability!subAvailabilityChild!chkTE = False
If IsNull(early) = False And x = 2 Then
Forms!availability!subAvailabilityChild!chkWE = True
If IsNull(early) = True And x = 2 Then
Forms!availability!subAvailabilityChild!chkWE = False
If IsNull(early) = False And x = 3 Then
Forms!availability!subAvailabilityChild!chkThE = True
If IsNull(early) = True And x = 3 Then
Forms!availability!subAvailabilityChild!chkThE = False
If IsNull(early) = False And x = 4 Then
Forms!availability!subAvailabilityChild!chkFE = True
If IsNull(early) = True And x = 4 Then
Forms!availability!subAvailabilityChild!chkFE = False
If IsNull(early) = False And x = 5 Then
Forms!availability!subAvailabilityChild!chkSaE = True
If IsNull(early) = True And x = 5 Then
Forms!availability!subAvailabilityChild!chkSaE = False
If IsNull(early) = False And x = 6 Then
Forms!availability!subAvailabilityChild!chkSuE = True
If IsNull(early) = True And x = 6 Then
Forms!availability!subAvailabilityChild!chkSuE = False

x = x + 1

Loop
Nov 27 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.