Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Dlookup, Dcount - syntax? Using wrong function?

Discussion in 'Information Technology' started by saraqpost@yahoo.com, Jul 28, 2006.

  1. saraqpost@yahoo.com

    saraqpost@yahoo.com
    Expand Collapse
    Guest

    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
     
  2. schasteen

    schasteen
    Expand Collapse
    Guest

    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
    >
    >
     
  3. John W. Vinson/MVP

    John W. Vinson/MVP
    Expand Collapse
    Guest

    <saraqpost@yahoo.com> wrote in message
    news:1149107147.949992.173280@i40g2000cwc.googlegroups.com...
    > 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



    You're making it harder than it needs to be. Text fields can be delimited
    with ' rather than ", and you can include ' directly in the text string -
    you don't need a separate & for every little bit of the constant text. Try

    intcount = DCount("[TrailerInfoKey]", "tblOutboundTrailer", _
    "[Date] = #" & Me.txtDate & "# AND [Status] = 'A'")

    When Access concatenates the pieces, the third argument will simply be

    [Date] = #5/30/2006# AND [Status] = 'A'

    Note that this WILL fail if the (badly named, it's a reserved word) field
    Date contains a time portion.


    > 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.


    Try

    intcount = DCount("[TrailerInfoKey]", "tblOutboundTrailer", _
    "[Date] = #" & Me.txtDate & "# AND [Status] = 'A' AND [TrailerNum] = """
    _
    & [strTrailerNum] & """")

    This includes two consecutive doublequotes before strTrailerNum and two
    consecutive doublequotes after, all delimited by doublequotes, just in case
    the form control named strTrailerNum might contain a ' character. If
    strTrailerNum is a VBA variable rather than a form control, you should
    remove the square brackets around it.


    --
    John W. Vinson/MVP
     
  4. schasteen

    schasteen
    Expand Collapse
    Guest

    Johns post made me relize I probably hav an error. If strTrailerNum is a
    variable in your code then

    intcount = DCount("[TrailerInfoKey]", "tblOutboundTrailer", "[Date] = #" &
    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
    > >
    > >
     
  5. saraqpost@yahoo.com

    saraqpost@yahoo.com
    Expand Collapse
    Guest

    Re: Dlookup, Dcount - syntax? Using wrong function?

    THANK YOU!!
    First, I had wondered about the field "Date" and thought I was getting
    away with it, but your comment forced me to change it throughout my
    little app. Thanks. (I knew it was the right thing to do, and
    hopefully won't make the same mistake again .)

    Second, the syntax worked! Yes, strTrailerNum is a variable in the
    code - I set it up in one of my million attempts to get the DCount to
    work - figuring if I put the data in a variable I could try to figure
    out the syntax more easily. I left the variable in; no harm done,
    right?

    Again, thanks - I'm going to try it in another section of code now.
    With your explanation, I am hoping I'll figure it out before the sun
    comes up tomorrow.

    sara




    John W. Vinson/MVP wrote:
    > <saraqpost@yahoo.com> wrote in message
    > news:1149107147.949992.173280@i40g2000cwc.googlegroups.com...
    > > 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

    >
    >
    > You're making it harder than it needs to be. Text fields can be delimited
    > with ' rather than ", and you can include ' directly in the text string -
    > you don't need a separate & for every little bit of the constant text. Try
    >
    > intcount = DCount("[TrailerInfoKey]", "tblOutboundTrailer", _
    > "[Date] = #" & Me.txtDate & "# AND [Status] = 'A'")
    >
    > When Access concatenates the pieces, the third argument will simply be
    >
    > [Date] = #5/30/2006# AND [Status] = 'A'
    >
    > Note that this WILL fail if the (badly named, it's a reserved word) field
    > Date contains a time portion.
    >
    >
    > > 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.

    >
    > Try
    >
    > intcount = DCount("[TrailerInfoKey]", "tblOutboundTrailer", _
    > "[Date] = #" & Me.txtDate & "# AND [Status] = 'A' AND [TrailerNum] = """
    > _
    > & [strTrailerNum] & """")
    >
    > This includes two consecutive doublequotes before strTrailerNum and two
    > consecutive doublequotes after, all delimited by doublequotes, just in case
    > the form control named strTrailerNum might contain a ' character. If
    > strTrailerNum is a VBA variable rather than a form control, you should
    > remove the square brackets around it.
    >
    >
    > --
    > John W. Vinson/MVP
     
  6. saraqpost@yahoo.com

    saraqpost@yahoo.com
    Expand Collapse
    Guest

    Re: Dlookup, Dcount - syntax? Using wrong function?

    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] = #" &
    > 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
    > > >
    > > >
     
  7. saraqpost@yahoo.com

    saraqpost@yahoo.com
    Expand Collapse
    Guest

    Re: Dlookup, Dcount - syntax? Using wrong function?

    Well, perhaps I got a little ahead of myself here.

    I tried a more complex lookup, and I have a few problems:

    1. I can only get it to compile if I put the entire string on one line.
    I thought I just put a continuation character at the end of the line
    and continued. I know with SQL you put an & and a " to continue as
    well, but that didn't work either.

    intcount = DCount("[OutboundTrailerDetailsKey]",
    "tblOutboundTrailerDetails", "[DetailsStatus] = 'A' AND [GoodsTypeKey]
    = lngGoodsType AND [LocationKey] = lngLocationKey AND [TrailerInfoKey]
    = lngTrailerInfoKey AND [DeptKey] = lngDeptKey")


    2. I'm getting a strange message:
    Error 2471 The expression you entered as a query parameter produced
    this error:
    The object doesn't contain the automation object lngGoodsType

    I set each of these fields as variables in the code, as me. didn't
    work.

    I did a MsgBox and they all show up (goodstype is a combo box; value =
    3)

    I'm not sure - it seems it might have something to do with the single
    quote then the AND?? Is this written anywhere in help or on the web to
    understand at all? It's confusing and more than a little frustrating
    right now!!

    thanks
    sara
     
  8. schasteen

    schasteen
    Expand Collapse
    Guest

    Re: Dlookup, Dcount - syntax? Using wrong function?

    Your variables need to be pulled out of the string. Right now your code is
    looking for [LocationKey] = lngLocationKey not the value of lngLocationKey.
    I am unsure of the datatype so I will assume all numbers from the variable
    names

    intcount = DCount("[OutboundTrailerDetailsKey]",
    "tblOutboundTrailerDetails", "[DetailsStatus] = 'A' AND [GoodsTypeKey] = " &
    lngGoodsType & " AND [LocationKey] = " & lngLocationKey & " AND
    [TrailerInfoKey] = & " lngTrailerInfoKey & " AND [DeptKey] = " & lngDeptKey)


    "saraqpost@yahoo.com" wrote:

    > Well, perhaps I got a little ahead of myself here.
    >
    > I tried a more complex lookup, and I have a few problems:
    >
    > 1. I can only get it to compile if I put the entire string on one line.
    > I thought I just put a continuation character at the end of the line
    > and continued. I know with SQL you put an & and a " to continue as
    > well, but that didn't work either.
    >
    > intcount = DCount("[OutboundTrailerDetailsKey]",
    > "tblOutboundTrailerDetails", "[DetailsStatus] = 'A' AND [GoodsTypeKey]
    > = lngGoodsType AND [LocationKey] = lngLocationKey AND [TrailerInfoKey]
    > = lngTrailerInfoKey AND [DeptKey] = lngDeptKey")
    >
    >
    > 2. I'm getting a strange message:
    > Error 2471 The expression you entered as a query parameter produced
    > this error:
    > The object doesn't contain the automation object lngGoodsType
    >
    > I set each of these fields as variables in the code, as me. didn't
    > work.
    >
    > I did a MsgBox and they all show up (goodstype is a combo box; value =
    > 3)
    >
    > I'm not sure - it seems it might have something to do with the single
    > quote then the AND?? Is this written anywhere in help or on the web to
    > understand at all? It's confusing and more than a little frustrating
    > right now!!
    >
    > thanks
    > sara
    >
    >
     
  9. saraqpost@yahoo.com

    saraqpost@yahoo.com
    Expand Collapse
    Guest

    Re: Dlookup, Dcount - syntax? Using wrong function?

    I understand what you've said here - but I can't get it to compile. I
    have tried NOT continuing the code (on multiple lines), but that didn't
    work. Is it the ) at the end? Just stays RED in VBA.

    thanks - sorry to be a pest about this. It's a toughie for me!
    Sara


    schasteen wrote:
    > Your variables need to be pulled out of the string. Right now your code is
    > looking for [LocationKey] = lngLocationKey not the value of lngLocationKey.
    > I am unsure of the datatype so I will assume all numbers from the variable
    > names
    >
    > intcount = DCount("[OutboundTrailerDetailsKey]",
    > "tblOutboundTrailerDetails", "[DetailsStatus] = 'A' AND [GoodsTypeKey] = " &
    > lngGoodsType & " AND [LocationKey] = " & lngLocationKey & " AND
    > [TrailerInfoKey] = & " lngTrailerInfoKey & " AND [DeptKey] = " & lngDeptKey)
    >
    >
    > "saraqpost@yahoo.com" wrote:
    >
    > > Well, perhaps I got a little ahead of myself here.
    > >
    > > I tried a more complex lookup, and I have a few problems:
    > >
    > > 1. I can only get it to compile if I put the entire string on one line.
    > > I thought I just put a continuation character at the end of the line
    > > and continued. I know with SQL you put an & and a " to continue as
    > > well, but that didn't work either.
    > >
    > > intcount = DCount("[OutboundTrailerDetailsKey]",
    > > "tblOutboundTrailerDetails", "[DetailsStatus] = 'A' AND [GoodsTypeKey]
    > > = lngGoodsType AND [LocationKey] = lngLocationKey AND [TrailerInfoKey]
    > > = lngTrailerInfoKey AND [DeptKey] = lngDeptKey")
    > >
    > >
    > > 2. I'm getting a strange message:
    > > Error 2471 The expression you entered as a query parameter produced
    > > this error:
    > > The object doesn't contain the automation object lngGoodsType
    > >
    > > I set each of these fields as variables in the code, as me. didn't
    > > work.
    > >
    > > I did a MsgBox and they all show up (goodstype is a combo box; value =
    > > 3)
    > >
    > > I'm not sure - it seems it might have something to do with the single
    > > quote then the AND?? Is this written anywhere in help or on the web to
    > > understand at all? It's confusing and more than a little frustrating
    > > right now!!
    > >
    > > thanks
    > > sara
    > >
    > >
     
  10. Ron2006

    Ron2006
    Expand Collapse
    Guest

    Re: Dlookup, Dcount - syntax? Using wrong function?

    As a workaround.

    use the query wizard and work up the query there.

    If you open the form and load the fields etc and then go to the query
    area you can test the query.

    Then change the dcount to use the query you just created instead.
     
  11. schasteen

    schasteen
    Expand Collapse
    Guest

    Re: Dlookup, Dcount - syntax? Using wrong function?

    If you coppied my sting exactly and did not make any changes, I had one typo.
    This should work all on one line
    intcount = DCount("[OutboundTrailerDetailsKey]",
    "tblOutboundTrailerDetails", "[DetailsStatus] = "'A' AND [GoodsTypeKey] = " &
    lngGoodsType & " AND [LocationKey] = " & lngLocationKey & " AND
    [TrailerInfoKey] = " & lngTrailerInfoKey & " AND [DeptKey] = " & lngDeptKey)

    If you want to split, you have to be carefully where you choose to split it
    at. Try this

    INTCOUNT = DCount("[OutboundTrailerDetailsKey]",
    "tblOutboundTrailerDetails", "" _
    & "[DetailsStatus] = 'A' " _
    & "AND [GoodsTypeKey] = " & lngGoodsType & " " _
    & "AND [LocationKey] = " & lngLocationKey & " " _
    & "AND [TrailerInfoKey] = " & lngTrailerInfoKey & " " _
    & "AND [DeptKey] = " & lngDeptKey)

    Be carefull with the way this may wrap in your browser

    "saraqpost@yahoo.com" wrote:

    > I understand what you've said here - but I can't get it to compile. I
    > have tried NOT continuing the code (on multiple lines), but that didn't
    > work. Is it the ) at the end? Just stays RED in VBA.
    >
    > thanks - sorry to be a pest about this. It's a toughie for me!
    > Sara
    >
    >
    > schasteen wrote:
    > > Your variables need to be pulled out of the string. Right now your code is
    > > looking for [LocationKey] = lngLocationKey not the value of lngLocationKey.
    > > I am unsure of the datatype so I will assume all numbers from the variable
    > > names
    > >
    > > intcount = DCount("[OutboundTrailerDetailsKey]",
    > > "tblOutboundTrailerDetails", "[DetailsStatus] = 'A' AND [GoodsTypeKey] = " &
    > > lngGoodsType & " AND [LocationKey] = " & lngLocationKey & " AND
    > > [TrailerInfoKey] = & " lngTrailerInfoKey & " AND [DeptKey] = " & lngDeptKey)
    > >
    > >
    > > "saraqpost@yahoo.com" wrote:
    > >
    > > > Well, perhaps I got a little ahead of myself here.
    > > >
    > > > I tried a more complex lookup, and I have a few problems:
    > > >
    > > > 1. I can only get it to compile if I put the entire string on one line.
    > > > I thought I just put a continuation character at the end of the line
    > > > and continued. I know with SQL you put an & and a " to continue as
    > > > well, but that didn't work either.
    > > >
    > > > intcount = DCount("[OutboundTrailerDetailsKey]",
    > > > "tblOutboundTrailerDetails", "[DetailsStatus] = 'A' AND [GoodsTypeKey]
    > > > = lngGoodsType AND [LocationKey] = lngLocationKey AND [TrailerInfoKey]
    > > > = lngTrailerInfoKey AND [DeptKey] = lngDeptKey")
    > > >
    > > >
    > > > 2. I'm getting a strange message:
    > > > Error 2471 The expression you entered as a query parameter produced
    > > > this error:
    > > > The object doesn't contain the automation object lngGoodsType
    > > >
    > > > I set each of these fields as variables in the code, as me. didn't
    > > > work.
    > > >
    > > > I did a MsgBox and they all show up (goodstype is a combo box; value =
    > > > 3)
    > > >
    > > > I'm not sure - it seems it might have something to do with the single
    > > > quote then the AND?? Is this written anywhere in help or on the web to
    > > > understand at all? It's confusing and more than a little frustrating
    > > > right now!!
    > > >
    > > > thanks
    > > > sara
    > > >
    > > >

    >
    >
     
  12. saraqpost@yahoo.com

    saraqpost@yahoo.com
    Expand Collapse
    Guest

    Re: Dlookup, Dcount - syntax? Using wrong function?

    WOW! This is amazing - THANK YOU!!! I have to study more and see what
    you did - but I just had to try it first and it works!

    I see that I needed a space " " you added in a few places.

    I appreciate this more than you'll ever know....
    Sara


    schasteen wrote:
    > If you coppied my sting exactly and did not make any changes, I had one typo.
    > This should work all on one line
    > intcount = DCount("[OutboundTrailerDetailsKey]",
    > "tblOutboundTrailerDetails", "[DetailsStatus] = "'A' AND [GoodsTypeKey] = " &
    > lngGoodsType & " AND [LocationKey] = " & lngLocationKey & " AND
    > [TrailerInfoKey] = " & lngTrailerInfoKey & " AND [DeptKey] = " & lngDeptKey)
    >
    > If you want to split, you have to be carefully where you choose to split it
    > at. Try this
    >
    > INTCOUNT = DCount("[OutboundTrailerDetailsKey]",
    > "tblOutboundTrailerDetails", "" _
    > & "[DetailsStatus] = 'A' " _
    > & "AND [GoodsTypeKey] = " & lngGoodsType & " " _
    > & "AND [LocationKey] = " & lngLocationKey & " " _
    > & "AND [TrailerInfoKey] = " & lngTrailerInfoKey & " " _
    > & "AND [DeptKey] = " & lngDeptKey)
    >
    > Be carefull with the way this may wrap in your browser
    >
    > "saraqpost@yahoo.com" wrote:
    >
    > > I understand what you've said here - but I can't get it to compile. I
    > > have tried NOT continuing the code (on multiple lines), but that didn't
    > > work. Is it the ) at the end? Just stays RED in VBA.
    > >
    > > thanks - sorry to be a pest about this. It's a toughie for me!
    > > Sara
    > >
    > >
    > > schasteen wrote:
    > > > Your variables need to be pulled out of the string. Right now your code is
    > > > looking for [LocationKey] = lngLocationKey not the value of lngLocationKey.
    > > > I am unsure of the datatype so I will assume all numbers from the variable
    > > > names
    > > >
    > > > intcount = DCount("[OutboundTrailerDetailsKey]",
    > > > "tblOutboundTrailerDetails", "[DetailsStatus] = 'A' AND [GoodsTypeKey] = " &
    > > > lngGoodsType & " AND [LocationKey] = " & lngLocationKey & " AND
    > > > [TrailerInfoKey] = & " lngTrailerInfoKey & " AND [DeptKey] = " & lngDeptKey)
    > > >
    > > >
    > > > "saraqpost@yahoo.com" wrote:
    > > >
    > > > > Well, perhaps I got a little ahead of myself here.
    > > > >
    > > > > I tried a more complex lookup, and I have a few problems:
    > > > >
    > > > > 1. I can only get it to compile if I put the entire string on one line.
    > > > > I thought I just put a continuation character at the end of the line
    > > > > and continued. I know with SQL you put an & and a " to continue as
    > > > > well, but that didn't work either.
    > > > >
    > > > > intcount = DCount("[OutboundTrailerDetailsKey]",
    > > > > "tblOutboundTrailerDetails", "[DetailsStatus] = 'A' AND [GoodsTypeKey]
    > > > > = lngGoodsType AND [LocationKey] = lngLocationKey AND [TrailerInfoKey]
    > > > > = lngTrailerInfoKey AND [DeptKey] = lngDeptKey")
    > > > >
    > > > >
    > > > > 2. I'm getting a strange message:
    > > > > Error 2471 The expression you entered as a query parameter produced
    > > > > this error:
    > > > > The object doesn't contain the automation object lngGoodsType
    > > > >
    > > > > I set each of these fields as variables in the code, as me. didn't
    > > > > work.
    > > > >
    > > > > I did a MsgBox and they all show up (goodstype is a combo box; value =
    > > > > 3)
    > > > >
    > > > > I'm not sure - it seems it might have something to do with the single
    > > > > quote then the AND?? Is this written anywhere in help or on the web to
    > > > > understand at all? It's confusing and more than a little frustrating
    > > > > right now!!
    > > > >
    > > > > thanks
    > > > > sara
    > > > >
    > > > >

    > >
    > >
     
  13. saraqpost@yahoo.com

    saraqpost@yahoo.com
    Expand Collapse
    Guest

    Re: Dlookup, Dcount - syntax? Using wrong function?

    What do I write the query looking for? I use this technique when
    loading a listbox from code - write the select query, then copy the SQL
    into code and adjust. I am pretty comfortable with that. But I had no
    idea what query to create for the DLookup. Any hints?

    thanks
    sara


    Ron2006 wrote:
    > As a workaround.
    >
    > use the query wizard and work up the query there.
    >
    > If you open the form and load the fields etc and then go to the query
    > area you can test the query.
    >
    > Then change the dcount to use the query you just created instead.
     
  14. Ron2006

    Ron2006
    Expand Collapse
    Guest

    Re: Dlookup, Dcount - syntax? Using wrong function?

    I am confused now also. I thought you just got what you needed from the
    prior posting.

    What I was trying to say is when I use DCount or DLookup etc., what I
    oftern do is instead of trying to script out the WHERE/condition part
    that you can use of those functions, instead if go to the query builder
    and write the query there using all the tools that query builder gives
    me and then simply save that query. Then instead of writing the DCount
    to do a count on the table with this complex condition statement I
    simply do the DCount on the query I just saved.

    That way I usually don't have to worry anywhere as much about single
    and double quotes and # signs, since the wizard handles a lot of that
    for me.

    And where it would be using passed information I tend to set up global
    variables or a hiddenform where I load fields to use are arguments.
    That way the same query can be used from multiple forms.
     

Share This Page