A quick DLookup question

[WayneRyan]

Nigel,

If the ProcessDate really is a date and the Completed field is a Boolean, then
this should work.

Dim rst As DAO.Recordset
Dim sql As String

sql = "Select * " & _
      "From   MonthlyTbl " & _
      "Where  [ProcessDate] Between #" & CDate(Month(DateAdd("m", -1, Date)) & "/5/" & Year(DateAdd("m", -1, Date))) & "# AND " & _
      "                             #" & CDate(Month(Date) & "/4/" & Year(Date)) & "# And "" & _
      "       Completed"

Set rst = CurrentDb.OpenRecordset(sql)
If rst.EOF And rst.BOf Then
   MsgBox "There are none completed within the last month."
   Exit Sub
End If
'
'  Loop through the (at least one) ProcessIDs that are completed.
'
While Not rst.EOF And Not rst.BOF
   MsgBox "This ProcessID was completed." & rst!ProcessID
   rst.MoveNext
   Wend

You were right in needing the # delimiters.

But the DLookup can return a Null. To handle that properly, the DLookUp should be assigned to a variant, OR use
the Nz function to provide a value.

I feel the recordset is easier.

hth,
Wayne

 

[NigelShaw]

Hi Wayne,

thank you very much. i will be trying this later tonight when i get chance but it seems great. as i have said before, im not that familiar with SQL but it reads pretty straight forward when you look at it.
its surprising though that there is very little info available on the "between" function. i have a 1500 page Access book and there is only 1 paragraph with 9 lines describing this function which basically states, " you can use between to look between 2 numbers" but doesnt show any examples. there isnt much on the internet either so i was struggling as i knew it was something to do with this. I would have thought there would be more information available but No!

i will let you know my findings later.

Many thanks and Wayne to the rescue again

NS

 

[Pat Hartman]

If you refer back to post #16, you removed the DteCurrPerd variable from the DLookup(). It needs to go back. The string it contains says something like -
"Between #1/1/1# And #2/2/2#" so comparing it to another field would be like comparing the string value to a date which will never return true. You need to use the string value as the where clause for the lookup query.

IF Nz(DLookup("ProcessDate", "MonthlyTbl", DteCurrPerd))

 

[NigelShaw]

Hi Pat,
still errors.

This code-

BldPrevDT = CDate(Month(DateAdd("m", -1, Date)) & "/5/" & Year(DateAdd("m", -1, Date)))
BldCurDT = CDate(Month(Date) & "/4/" & Year(Date))

DteCurrPerd = "Between #" & BldPrevDT & "# AND #" & BldCurDT & "#"

is building a string
Between #07/05/2008# AND #08/04/2008#
which i guess is correct. i had to set Dim DteCurrPerd as String and not Date as Date gave errors back. as a string, i have temporarily placed a textbox on my form to receive the result and i get one with it set a string.

i have tested line by line with a small msgbox "true" inserted. if i get the True message, my code is working. marked as OK, the below describes what works so far-

Dim DteCurrPerd As String 'OK
Dim BldCurDT As Date 'OK
Dim BldPrevDT As Date 'OK

BldPrevDT = CDate(Month(DateAdd("m", -1, Date)) & "/5/" & Year(DateAdd("m", -1, Date))) 'OK
BldCurDT = CDate(Month(Date) & "/4/" & Year(Date))'OK

DteCurrPerd = "Between #" & BldPrevDT & "# AND #" & BldCurDT & "#" 'OK
Forms!mainform!Text218.Value = DteCurrPerd 'OK

If Nz(DLookup("ProcessDate", "MonthlyTbl", DteCurrPerd)) Then 'ERROR

MsgBox ("You have already completed this period!")
Exit Sub

is it because DteCurrPerd is not a date therefore the Dlookup checking ProcessDate cannot check the dates against a string?

regs,

Nigel

 

[Pat Hartman]

I see what's missing -

DteCurrPerd = "yourdatefield Between #" & BldPrevDT & "# AND #" & BldCurDT & "#"

Replace yourdatefield with the name of the date field in the table that you are comparing to. The where argument needs to be a complete where argument without the "WHERE" and the field name was missing.

 

[NigelShaw]

Hi Pat,

its like an ongoing saga.....

i have changed it to
DteCurrPerd = "ProcessDate Between #" & BldPrevDT & "# AND #" & BldCurDT & "#"

this line will now look up the date i removed ALL data from the table and run the code. no record was there so i was told the month had NOT been completed. i placed in a date between the period and i got the other message saying the month HAS been completed.

i then moved my PC date forward 2 months to check. it still gives me the message that the month HAS been completed even though 2 mon ths ahead, the entered date is out of the between range therefore should not be included.


NS

 

[NigelShaw]

I think i might know what the problem is but please correct me if i am wrong.

ProcessDate is entered dd/mm/yyyy

DtCurrPerd is written in the string mm/dd/yyyy

with the ProcessDate format need to match the DTCurrPerd string format?

NS

 

[NigelShaw]

I think ive done it!

for what ever reason, the code wasnt working. there seemed to be a date integrity issue as i set up a simple code to test dates and my results cam back as false. i dod though, use the basis of the code to do the following-

My mainform is always open and never closes
i placed 2 txtboxes on the form ( start ) & (end )

put the Cdate - 1 month on variable1
put the Cdate current month on Variable2

fill the dates OnLoad to give me a current period.

created a query that looked at the latest ProcessDate and set criteria
Between forms!MainForm!StartTxt AND Forms!Mainform"EndTxt

works great.

only problem now is i am a little stubborn in the way of, i know it can be done by code and you dont need a query so what is that way? i will continue to find an answer for arguments sake and satisfaction as it has had me for near on 4 days now!

just need to set up my period check to keep the period on month change and i'll be good to go.

thanks to everyone for the help and advice, i do really appreciate the input.

regs,

NigelShaw

 

[NigelShaw]

Phew! just seen the 10,000 mark for Ken. Well done. hopefully, if i ever get there, im not commented on the fact that probably 50% of my posts are infact - er - to me!!

getting back.

Sorry to bring this up again. there is nothing wrong with the solution i have which is to check between the dates with a query. this all works fine. more so, i have a period issue when the month changes.

currently,

if i am in say August, my period is set from 5th Aug to 4th Sep which is correct however, when the month changes, my eriod changes to 5th Sep to 4th Oct which is not correct.

Dates seem to be a tricky grey area given the fact that its probably a fundamental tool in a database however, all date examples with Access seem to month to Month end and not between to dates across 2 months.

i need to set up a date check that will firstly look up the current date.
dteCurrentDate = Date ' Todays Date

i have my period set
BldCurDT = CDate("4/" & Month(DateAdd("m", 1, Date)) & "/" & Year(DateAdd("m", 1, Date)))
BldPrevDT = CDate("5/" & Month(Date) & "/" & Year(Date))

Thus above code giving me today as 18/08/2008 and period 05/08/2008 to 04/09/2008

so how do i keep the period when the month changes?

i need to get todays date to check between the period dates and keep the period even into the next month but im not entirely sure of the coding!

i think it may have to check between 1st & 4th of every month but im a bit lost.

does anyone have any ideas?


many thanks,


Nigel

 

[NigelShaw]

Hi anyone?

in all fairness, this should work

Dim dtenowdate As Date
Dim dteChkCntDateSrt As Date
Dim dteChkCntDateFsh As Date

'set todays date
dtenowdate = Date
'check period this month at 1st day
dteChkCntDateSrt = CDate("1/" & Month(Date) & "/" & Year(Date))

'check period this month at 4th day
dteChkCntDateFsh = CDate("4/" & Month(Date) & "/" & Year(Date))

'if todays date is between 1st & 4th day of this month
If dtenowdate <> ("Between #" & dteChkCntDateSrt & "# AND #" & dteChkCntDateFsh & "#") Then

'show this box to state the period was last month
MsgBox ("current date = " & dtenowdate & vbCrLf _
& "period start = last month start" & dteChkCntDateSrt & vbCrLf _
& "period end = last month end" & dteChkCntDateFsh)
'otherwise
Else
'show this box to show period this month
MsgBox ("current date = " & dtenowdate & vbCrLf _
& "period start = this month start" & dteChkCntDateSrt & vbCrLf _
& "period end = this month end" & dteChkCntDateFsh)
End If

so, checking the date. if it is between 1st & 4th, this is the end of the last period so show last period. if the date is NOT between 1st & 4th, then you are in a new period of the current month.

only,

i get the same message no matter what date i am. again, i think there is an issue with the "between" line.

do i have my syntax right? the variables are set as dates!

thanks,


Nigel

 

[Pat Hartman]

Your dates need to be month/day/year for the between to work. SQL assumes date strings to be in US date format.

 

[NigelShaw]

hi Pat,

That could explain it though I voted for a temporay solution of placing the 2 dates on the form as hidden and then using a query to look between.

I would prefer hard code as potentially, a use could mistakenly alter something so to prevent that, I made a sequence to clear the table when it closes and re write the data when it opens. Probably not the best way but seems to be quite stable.

I will mess around with the dates but not today as I've been on them all week and now I have a headache!

Regs,

Nigel