Sikh Philosophy Network - View Single Post - Dlookup, Dcount

#6 (**permalink**) 28-Jul-2006, 08:14 AM

Got it - repied to John's post. MANY thanks!!
Sara

schasteen wrote:
> Johns post made me relize I probably hav an error. If strTrailerNum is a
> variable in your code then
>
> intcount = DCount("[TrailerInfoKey]", "tblOutboundTrailer", "[Date] = #" &
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/10635-dlookup-dcount-syntax-using-wrong-function.html
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10635
> Me.txtDate & "#" & " AND [Status] = 'A' and [TrailerNum] = '" &
> strTrailerNum & "'")
>
> "schasteen" wrote:
>
> > Try
> > intcount = DCount("[TrailerInfoKey]", "tblOutboundTrailer", "[Date] = #" &
> > Me.txtDate & "#" & " AND [Status] = 'A' and [TrailerNum] = '" &
> > [strTrailerNum] & "'")
> >
> > "saraqpost@yahoo.com" wrote:
> >
> > > I'm trying to check to see if the user has already added a Truck/Date
> > > combination (with Status = A), before the accmdSaveRecord.
> > >
> > > I have been trying Dlookup and Dcount, in various combinations, to
> > > accomplish this, but no luck. I can't get the complex statement to
> > > work, and when I Dcount just the TrailerNum (text field) I get 0, but
> > > there is a record on file.
> > >
> > > Here's the situation and code:
> > > Table: tblOutboundTrailer
> > > Key: TrailerInfoKey
> > > Fields (fieldname on frmNewTrailer): TrailerNum (me.txtTrailerNum)
> > > Date (me.date)
> > > Status (Defaults to "A". If a user requests 'delete a
> > > trailer' I change the status to "D")
> > >
> > > Code:
> > > intcount = DCount("[TrailerInfoKey]", "tblOutboundTrailer", _
> > > "[Date] = #" & Me.txtDate & "#") - WORKS! I get that the date is
> > > on the table 13 times
> > >
> > > intcount = DCount("[TrailerInfoKey]", "tblOutboundTrailer", _
> > > "[Date] = #" & Me.txtDate & "#" & " AND [Status] = " & """ A & """)
> > > - Compiles, but DOESN'T WORK - intcount is 0; there are 12 records with
> > > the date and status = A
> > >
> > > intcount = DCount("[TrailerInfoKey]", "tblOutboundTrailer", _
> > > "[Date] = #" & Me.txtDate & "#" & " AND [Status] = " & """ A & """
> > > _
> > > & " AND [TrailerNum] = " & """ [strTrailerNum] & """) - Compiles,
> > > but DOESN'T WORK - intcount is 0; there is 1 record with the date and
> > > status = A and TrailerNum 1.
> > >
> > > Any complex statements don't work. The simple one does. Can't figure
> > > this out!
> > >
> > > Thanks
> > > Sara
> > >
> > >