Prev: Sorting bill of materials references
Next: Access 2007 training by TrainSignal-Any good?
From: whiz on 13 Mar 2010 19:18
I'm using the DCount function in VBA. I need some other pair of eyes
to locate my problem in the WHERE clause portion. Below is one
statement of the code:

rec2("PR") = DCount("[PN]", strProgram, _
"([PROC] = " & "BUYIH" & " Or [PROC] = " & "BUY" & " Or [PROC]
= " & "BUYDS" & ") AND ([PO#PR] Like " & "G" & " & " & "*" & ")")

So, basically I want a count of PN from a table where PROC is equal to
BUYIH, BUY, or, BUYDS AND PO#PR is like G and *. Any help is
appreciated. After looking at it for awhile, the obvious don't stick
out. Thanks.
From: Allen Browne on 14 Mar 2010 03:00
You are trying to concatenate values into the 3rd argument for DCount().
You'll find it easier to get this right if you create a string, and then
test it in the Immediate window (Ctrl+G) to see what when wrong.)

This kind of thing:
Dim strWhere As String
strWhere = "(BUYIH IN (""BUY"", ""BUYDS"")) AND ([PO#PR] Like ""G*"")"
'Debug.Print strWhere
rec2("PR") = DCount("[PN]", strProgram, strWhere)

If the quotes don't make sense to you, here's an explanation:
http://allenbrowne.com/casu-17.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"whiz" <whizdogbob76(a)gmail.com> wrote in message
news:1a3d4efc-12e7-490b-8ce6-e6f33bccf897(a)x12g2000yqx.googlegroups.com...
> I'm using the DCount function in VBA. I need some other pair of eyes
> to locate my problem in the WHERE clause portion. Below is one
> statement of the code:
>
> rec2("PR") = DCount("[PN]", strProgram, _
> "([PROC] = " & "BUYIH" & " Or [PROC] = " & "BUY" & " Or [PROC]
> = " & "BUYDS" & ") AND ([PO#PR] Like " & "G" & " & " & "*" & ")")
>
> So, basically I want a count of PN from a table where PROC is equal to
> BUYIH, BUY, or, BUYDS AND PO#PR is like G and *. Any help is
> appreciated. After looking at it for awhile, the obvious don't stick
> out. Thanks.

From: Roger on 14 Mar 2010 10:02
On Mar 13, 6:18 pm, whiz <whizdogbo...(a)gmail.com> wrote:
> I'm using the DCount function in VBA. I need some other pair of eyes
> to locate my problem in the WHERE clause portion. Below is one
> statement of the code:
>
> rec2("PR") = DCount("[PN]", strProgram, _
>         "([PROC] = " & "BUYIH" & " Or [PROC] = " & "BUY" & " Or [PROC]
> = " & "BUYDS" & ") AND ([PO#PR] Like " & "G" & " & " & "*" & ")")
>
> So, basically I want a count of PN from a table where PROC is equal to
> BUYIH, BUY, or, BUYDS AND PO#PR is like G and *. Any help is
> appreciated. After looking at it for awhile, the obvious don't stick
> out. Thanks.

strWhere = "[PROC] IN ('BUYIH','BUY','BUYDS') AND " & _
"([PO#PR] LIKE 'G' OR " & _
" [PO#PR] LIKE '*')"
rec2("PR") = dcount("[PN]", strProgram, strWhere)
From: Salad on 14 Mar 2010 12:26
Roger wrote:
> On Mar 13, 6:18 pm, whiz <whizdogbo...(a)gmail.com> wrote:
>
>>I'm using the DCount function in VBA. I need some other pair of eyes
>>to locate my problem in the WHERE clause portion. Below is one
>>statement of the code:
>>
>>rec2("PR") = DCount("[PN]", strProgram, _
>> "([PROC] = " & "BUYIH" & " Or [PROC] = " & "BUY" & " Or [PROC]
>>= " & "BUYDS" & ") AND ([PO#PR] Like " & "G" & " & " & "*" & ")")
>>
>>So, basically I want a count of PN from a table where PROC is equal to
>>BUYIH, BUY, or, BUYDS AND PO#PR is like G and *. Any help is
>>appreciated. After looking at it for awhile, the obvious don't stick
>>out. Thanks.
>
>
> strWhere = "[PROC] IN ('BUYIH','BUY','BUYDS') AND " & _
> "([PO#PR] LIKE 'G' OR " & _
> " [PO#PR] LIKE '*')"
> rec2("PR") = dcount("[PN]", strProgram, strWhere)

Your like statement evaluates to
G & *
Is that the pattern you are searching for?

You could use Instr().

From: Marco Pagliero on 19 Mar 2010 18:59
On 14 Mrz., 01:18, whiz wrote:
> So, basically I want a count of PN from a table where PROC is equal to
> BUYIH, BUY, or, BUYDS AND PO#PR is like G and *.

If G is the letter G
"([PROC] = 'BUYIH' Or [PROC] = 'BUY' Or [PROC] = 'BUYDS') AND ([PO#PR]
Like 'G*')"

If G is a string variable
"([PROC] = 'BUYIH' Or [PROC] = 'BUY' Or [PROC] = 'BUYDS') AND ([PO#PR]
Like '" & G & "'*')"
 | 
Pages: 1
Prev: Sorting bill of materials references
Next: Access 2007 training by TrainSignal-Any good?