On May 6, 12:15*pm, Bob Quintal <rquin...@sPAmpatico.ca> wrote:
> Dave <david_l...@hotmail.com> wrote innews:4b979fa1-a962-411a-92d4-7b43d63e4f13@a3g2000vbm.googlegroups.com
> :
>
>
>
>
>
>
>
>
>
> > On May 5, 8:32*pm, Patrick Finucane <patrickfinucan...@gmail.com>
> > wrote:
> >> On May 5, 10:54*am, Dave <david_l...@hotmail.com> wrote:
>
> >> > Hi,
>
> >> > I first have a table called Holidays this has the fields
> >> > HolidayID (autonumber) and HolidayDate (Date), the holiday date
> >> > field has many dates with holidays and other dates the company
> >> > is off.
>
> >> > I then have a form for the orders with a Target Date on, i
> >> > wanted to able to tell the user with a message box, saying If
> >> > it was a Holiday or not. Thus telling the user to select a
> >> > different date.
>
> >> > I put an afterupdate event behind the Target Date in the form:
>
> >> > If Me.txtTargetDate = dLookup("HolidayDate",Holidays") Then
> >> > MsgBox "You've entered a Holiday, Please select another Date"
> >> > Else
> >> > End If
>
> >> > This code only seems to work for the first record in the table,
> >> > is there a way of getting the dlookup to work for all the
> >> > records in the table? If not is there another solution to the
> >> > problem, I'm not all that sure with coding in VBA.
>
> >> You might want to re-readthe help topic again regarding
> >> DLookup(). You missed something....kinda big and obvious.
>
> >> There are three parts; the field, the table/query, the filter.
> >> Where's your filter?
>
> >> You did prove to others the filter parameter is optional.
>
> > I don't really want a filter in the code, I just want the code to
> > look up all the values in the table (all the holiday dates), thats
> > why I didn't put a criteria in! I tried this code instead with a
> > criteria but it still comes up with errors.
>
> > If Me.txtTargetDate = dLookup("HolidayDate","Holidays","HolidayID
> > '*'") Then
> > *MsgBox "You've entered a Holiday, Please select another Date"
> > *Else
> > *End If
>
> You do want a filter, specifically to the date in the txtTargetDate,
>
> try:
> If Me.txtTargetDate = dLookup("HolidayDate","Holidays", _
> * *"HolidayDate = #" & Me.txtTargetDate & "@")
>
> --
> Bob Q.
> PA is y I've altered my address.

Hi Bob,

I tried the code without success, I keep getting a syntax error.

It says on the error:
Run-time error '3075'
Syntax error in date in query expression 'HolidayDate =
#07/05/12/2012@'.