Full Version: date compare
UtterAccess Forums > Microsoft® Access > Access Forms
ebwhittaker
Need a little help .... one table/form (Users) enters the InDate and Outdate of a single user and the other table (Group) holds ArrivalDate and DepartureDate of groups
need to allow the operator to enter the InDate and OutDate of the user, then run this against the list of Group ArrivalDate and DepartureDate entries to see if they overlap, even by a day. If so I need a popup stating such.
Otried using the Datediff, etc but dont seem to be able to get it to check all the records in the Group table against the single entry in the User form InDate and OutDate fields.
Any help would be most appreciated
best regards
ed
dabooj
try using the between command.
If [date entered] is between [date1] and [date2] then
end if
Please note that if dates are parsed as string values then yoiu need to wrap them in the hash ('#' & date1 & '#')
regards,
Shuja.
ebwhittaker
For some reason I get a syntax error saying that it appears the between function is not recognized ... at least the error says it expects a then or go to
This is what I entered in the On Exit Event area
If [In Date] Is between([Forms]![groupEvent]![arrivaldate]) And ([Forms]![groupEvent]![Departuredate]) Then Stop
The three fields are all formated as Date/Time
thanks
ed
SerranoG
For a form's VBA code, the BETWEEN function doesn't work. That's only for query or SQL criteria. Try
!--c1-->
CODE
If [In Date] >= [Forms]![groupEvent]![arrivaldate] And [In Date] <= [Forms]![groupEvent]![Departuredate] Then
    ... code here
End If
ebwhittaker
This is what is in the On Exit Event
If [In Date] >= [Forms]![GroupEvent]![arrivaldate] And [In Date] <= [Forms]![GroupEvent]![Departuredate] Then Stop
I get a cannot find GroupEvent Form error, yet it is there and is named GroupEvent
Any ideas?
thanks
ed
SerranoG
I don't know what you mean by
Please rephrase. Thanks.
ebwhittaker
When I open the main form and put in an InDate and Exit from that field a popup comes up and says that it cannot find Form GroupEvent yet there is a form called GroupEvent that contains fields ArrivalDate and DepartureDate
d
SerranoG
Here's a "Did you remember to plug it in?" type question. Although GroupForm may exist, the code will fail if it's not open at the time of that ON EXIT event. Is it open?

If not, it needs to be open. You can hide it if you don't want people to see it. If you don't want it open, you can use a DLOOKUP function to find the values of the two dates instead of getting them off the form.


Edited by: SerranoG on Wed Feb 27 8:50:46 EST 2008.
ebwhittaker
OK, well gee DAH .... of course it was NOT open .... Brain Freeze. I think I'll give the DLOOKUP function a try only because I am a large gluton for punishment person ....
Thanks
ed
ebwhittaker
Wow.. If I wasn't confused before this I certainly am now ... read probable fifty different sites on using the DLookup function and of course tried the majority without sucess ... so now I need your help, thank you in advance
able Name is Group_SubForm
Field Names are BeginDate and EndDate
Date to compare it to is InDate located on the Reservations Form
Need to see if InDate is >= BeginDate and <= EndDate and popup a MsgBox
thanks
ed
ebwhittaker
Here is the code I put in on the On Exit of the InDate .... In works great BUT only against the first record in the GroupEvent Table
If [InDate] >= DLookup("[BeginDate]", "GroupEvent", "[BeginDate]") And [InDate] <= DLookup("[EndDate]", "GroupEvent", "[EndDate]") Then
MsgBox "Notice - Possible Conflict with Group Event"
End If
Oalso dont seem to be able to append the MsgBox with the field from the GroupEvent called Activity
Thanks
Ed
ebwhittaker
Here is the code I am using - note that it does work, sort of
If DLookup("[BeginDate]", "GroupEvent", "[BeginDate] <= [In Date]") And DLookup
("[EndDate]", "GroupEvent", "[EndDate] >= [In Date]") Then pop up MsgBox
what happens it compares the BeginDates and EndDates from all the records and not one at atime so the popup will come up even if the InDate is between two of the BeginDate and EndDate record values
It seems like it is doing what it is written to do but unfortunatly not what I need it to do. Is there a way to have it deal with only one record at a time while still searching through all the GroupEvent BeginDate and EndDate entries?
Thanks
ed
ebwhittaker
This is what I finally came up with and it seems to work
If DLookup("[BeginDate]", "GroupdEvent", "[In Date] >= [BeginDate] And [In Date]<= [EndDate]") Then
MsgBox pops up
Now the next problem is to display the Activity field info that is in the GroupEvent table after the MsgBox message.
ebwhittaker
here is the code I put in the OnExit of the InDate
If DLookup("[BeginDate]", "GroupEvent", "[In Date] >= [BeginDate] And [In Date]<= [EndDate]") Then
It works great BUT I need to get the Activity field information from the record. I cannot use another DLookup cause it pulls the Activity information from the first records and not the found record. Any Ideas?
Thanks
Ed
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.