DCount involving 2 fields

[Will]

Hi, I had a DCount within a module on records in a table where CustSuffix =
0. I now need to DCount where CustSuffix = 0 and the type of cost Suffix
(Suffix in table) = G. I can't get both requirements to work and have tried
a number of different formats. At the moment I am getting an object
required error message.

This is the current code:

Function UpdateDistributionCost(Suff As String) ' adds new record if none
exists, prompting for cost, else updates existing distribution cost
Dim db As Database
Dim InvoiceAudit As Recordset
Dim DistCost As Currency
Dim Answer, Status, St As String
Dim OrigValue As Currency
Dim Resp As Long
Dim DC As Variant
Resp = MsgBox("Do you wish to edit/add a frozen G Distribution Cost?",
vbYesNo, "Edit Distribution Cost")
If Resp = vbYes Then

If DCount("CustSuffix", "tblCustomerDisributionCost", "[CustSuffix]
= '" & Suff And [Suffix] = "G") = 0 Then
' NEED TO SET UP CUSTOMER DISTRIBUTION COST
Status = "new"
OrigValue = 0
..........snipped

Thanks

Will

[Wayne Morgan]

Close, try

DCount("CustSuffix", "tblCustomerDisributionCost", "[CustSuffix] = " & Suff
& " And [Suffix] = ""G""") = 0

Since you indicate Suff is a number, you don't need quotes around it. You
could also use single quotes instead of the multiple double quotes around G.
The advantage of the multiple double quotes is it will work if the value
contains a single quote, such as when searching last names (i.e. O'Hare). If
Suff is really a text value then:

DCount("CustSuffix", "tblCustomerDisributionCost", "[CustSuffix] = """ &
Suff & """ And [Suffix] = ""G""") = 0

--
Wayne Morgan
Microsoft Access MVP
"Will" <wi*****************@f2s.com> wrote in message
news:ch**********@news.freedom2surf.net...

Hi, I had a DCount within a module on records in a table where CustSuffix
=
0. I now need to DCount where CustSuffix = 0 and the type of cost Suffix
(Suffix in table) = G. I can't get both requirements to work and have
tried
a number of different formats. At the moment I am getting an object
required error message.

This is the current code:

Function UpdateDistributionCost(Suff As String) ' adds new record if none
exists, prompting for cost, else updates existing distribution cost
Dim db As Database
Dim InvoiceAudit As Recordset
Dim DistCost As Currency
Dim Answer, Status, St As String
Dim OrigValue As Currency
Dim Resp As Long
Dim DC As Variant
Resp = MsgBox("Do you wish to edit/add a frozen G Distribution Cost?",
vbYesNo, "Edit Distribution Cost")
If Resp = vbYes Then

If DCount("CustSuffix", "tblCustomerDisributionCost", "[CustSuffix]
= '" & Suff And [Suffix] = "G") = 0 Then
' NEED TO SET UP CUSTOMER DISTRIBUTION COST
Status = "new"
OrigValue = 0
..........snipped

Thanks

Will

[Douglas J. Steele]

If DCount("CustSuffix", "tblCustomerDisributionCost", "[CustSuffix] = '" &
Suff & "' And [Suffix] = 'G'") = 0 Then

exagerrated for clarity, the 3rd argument is

"[CustSuffix] = ' " & Suff & " ' And [Suffix] = ' G ' "

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"Will" <wi*****************@f2s.com> wrote in message
news:ch**********@news.freedom2surf.net...

Hi, I had a DCount within a module on records in a table where CustSuffix = 0. I now need to DCount where CustSuffix = 0 and the type of cost Suffix
(Suffix in table) = G. I can't get both requirements to work and have tried a number of different formats. At the moment I am getting an object
required error message.

This is the current code:

Function UpdateDistributionCost(Suff As String) ' adds new record if none
exists, prompting for cost, else updates existing distribution cost
Dim db As Database
Dim InvoiceAudit As Recordset
Dim DistCost As Currency
Dim Answer, Status, St As String
Dim OrigValue As Currency
Dim Resp As Long
Dim DC As Variant
Resp = MsgBox("Do you wish to edit/add a frozen G Distribution Cost?",
vbYesNo, "Edit Distribution Cost")
If Resp = vbYes Then

If DCount("CustSuffix", "tblCustomerDisributionCost", "[CustSuffix] = '" & Suff And [Suffix] = "G") = 0 Then
' NEED TO SET UP CUSTOMER DISTRIBUTION COST
Status = "new"
OrigValue = 0
..........snipped

Thanks

Will

[Will]

Thank you both your help, Suff is in fact really a text file.
Later on in the same part of code I need to need to Select on the same basis
as the DCount. Where Suffix = G

My current working code looks like this:
St = "SELECT * FROM tblCustomerDisributionCost WHERE [CustSuffix] ='" & Suff
& "';"
Set rs = db.OpenRecordset(St)
rs.Edit

I have tried copying the code from DCount and altering it but I keep getting
Compile error: Variable not Defined. Someone else has written this code and
I am editing it and don't really understand what the "';" does at the end of
the line? Any help would be grateful.

Thanks
"Wayne Morgan" <co***************************@hotmail.com> wrote in message
news:2q******************@newssvr33.news.prodigy.c om...

Close, try

DCount("CustSuffix", "tblCustomerDisributionCost", "[CustSuffix] = " & Suff & " And [Suffix] = ""G""") = 0

Since you indicate Suff is a number, you don't need quotes around it. You
could also use single quotes instead of the multiple double quotes around G. The advantage of the multiple double quotes is it will work if the value
contains a single quote, such as when searching last names (i.e. O'Hare). If Suff is really a text value then:

DCount("CustSuffix", "tblCustomerDisributionCost", "[CustSuffix] = """ &
Suff & """ And [Suffix] = ""G""") = 0

--
Wayne Morgan
Microsoft Access MVP
"Will" <wi*****************@f2s.com> wrote in message
news:ch**********@news.freedom2surf.net...

Hi, I had a DCount within a module on records in a table where CustSuffix =
0. I now need to DCount where CustSuffix = 0 and the type of cost Suffix (Suffix in table) = G. I can't get both requirements to work and have
tried
a number of different formats. At the moment I am getting an object
required error message.

This is the current code:

Function UpdateDistributionCost(Suff As String) ' adds new record if none exists, prompting for cost, else updates existing distribution cost
Dim db As Database
Dim InvoiceAudit As Recordset
Dim DistCost As Currency
Dim Answer, Status, St As String
Dim OrigValue As Currency
Dim Resp As Long
Dim DC As Variant
Resp = MsgBox("Do you wish to edit/add a frozen G Distribution Cost?", vbYesNo, "Edit Distribution Cost")
If Resp = vbYes Then

If DCount("CustSuffix", "tblCustomerDisributionCost", "[CustSuffix] = '" & Suff And [Suffix] = "G") = 0 Then
' NEED TO SET UP CUSTOMER DISTRIBUTION COST
Status = "new"
OrigValue = 0
..........snipped

Thanks

Will

[Wayne Morgan]

The ";" at the end of the line is just the standard way a SQL statement is
written. Open one of your queries in the query tab of the database window
and go to SQL view. You will notice they end with a ;.

In the code example you gave, the variables are St, Suff, and rs. Is there a
Dim statement in the procedure that has these listed? They may be in
separate Dim statements. If you are modifying the DCount code, show us what
you are coming up with after you make the modifications. You may have some
quotes in the wrong place, making Access think something is a variable when
you don't intend it to be.

--
Wayne Morgan
Microsoft Access MVP
"Will" <wi*****************@f2s.com> wrote in message
news:ch**********@news.freedom2surf.net...

Thank you both your help, Suff is in fact really a text file.
Later on in the same part of code I need to need to Select on the same
basis
as the DCount. Where Suffix = G

My current working code looks like this:
St = "SELECT * FROM tblCustomerDisributionCost WHERE [CustSuffix] ='" &
Suff
& "';"
Set rs = db.OpenRecordset(St)
rs.Edit

I have tried copying the code from DCount and altering it but I keep
getting
Compile error: Variable not Defined. Someone else has written this code
and
I am editing it and don't really understand what the "';" does at the end
of
the line? Any help would be grateful.

Thanks
"Wayne Morgan" <co***************************@hotmail.com> wrote in
message
news:2q******************@newssvr33.news.prodigy.c om...

Close, try

DCount("CustSuffix", "tblCustomerDisributionCost", "[CustSuffix] = " &

Suff

& " And [Suffix] = ""G""") = 0

Since you indicate Suff is a number, you don't need quotes around it. You
could also use single quotes instead of the multiple double quotes around

G.

The advantage of the multiple double quotes is it will work if the value
contains a single quote, such as when searching last names (i.e. O'Hare).

If

Suff is really a text value then:

DCount("CustSuffix", "tblCustomerDisributionCost", "[CustSuffix] = """ &
Suff & """ And [Suffix] = ""G""") = 0

--
Wayne Morgan
Microsoft Access MVP
"Will" <wi*****************@f2s.com> wrote in message
news:ch**********@news.freedom2surf.net...

> Hi, I had a DCount within a module on records in a table where CustSuffix > =
> 0. I now need to DCount where CustSuffix = 0 and the type of cost Suffix > (Suffix in table) = G. I can't get both requirements to work and have
> tried
> a number of different formats. At the moment I am getting an object
> required error message.
>
> This is the current code:
>
> Function UpdateDistributionCost(Suff As String) ' adds new record if none > exists, prompting for cost, else updates existing distribution cost
> Dim db As Database
> Dim InvoiceAudit As Recordset
> Dim DistCost As Currency
> Dim Answer, Status, St As String
> Dim OrigValue As Currency
> Dim Resp As Long
> Dim DC As Variant
>
>
> Resp = MsgBox("Do you wish to edit/add a frozen G Distribution Cost?", > vbYesNo, "Edit Distribution Cost")
> If Resp = vbYes Then
>
> If DCount("CustSuffix", "tblCustomerDisributionCost", "[CustSuffix] > = '" & Suff And [Suffix] = "G") = 0 Then
> ' NEED TO SET UP CUSTOMER DISTRIBUTION COST
> Status = "new"
> OrigValue = 0
> ..........snipped
>
> Thanks
>
> Will
>
>