Dlookup, Dcount - syntax? Using wrong function?

[saraqpost]

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

 

[Guest]

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

 

[John W. Vinson/MVP]

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.

 

[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 & "'")

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

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

 

[saraqpost]

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

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.

 

[saraqpost]

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

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 & "'")

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

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

 

[saraqpost]

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

 

[Guest]

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]

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

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)

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

 

[Ron2006]

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.

 

[Guest]

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

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

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)

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

 

[saraqpost]

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

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

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

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)


:

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

 

[saraqpost]

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]

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.