A quick DLookup question

[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

 

[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