query help

[Guest]

I have this query and it works fine:

B: Concatenate("SELECT A FROM abc WHERE Pal =" & [Pal])

Now I want to add two more criteria in it, so that it will
concatenate A with its right group where custcode, boxnr n
pal is matched, then it concatenate A..

A custcode boxnr B
--------------------------------
aa 01 b1 aabb
bb 01 b1 aabb
cc 02 c1 ccddee
dd 02 c1 ccddee
ee 02 c1 ccddee
ff 03 d1 ff

B: Concatenate("SELECT A FROM abc WHERE Pal =" & [Pal]
and custcode =" & [custcode] and boxnr =' & [boxnr])

Can anyone tell me what I did wrongly?

Thanks in advance

 

[John Vinson]

B: Concatenate("SELECT A FROM abc WHERE Pal =" & [Pal]
and custcode =" & [custcode] and boxnr =' & [boxnr])

Can anyone tell me what I did wrongly?

As often happens in this circumstance... you messed up your quotes.

When you're building a SQL string you need to a) determine what you
want the final string to look like, and b) build it up from string
constants delimited by " marks and from variables. In this case if
[Pal] is equal to 3, [custcode] is equal to 845, and [boxnr] is equal
to BK333, you want the SQL string to read (after it's concatenated)

SELECT A FROM abc WHERE Pal = 3 AND custcode = 845 AND boxnr = 'BK333'

So build it up in pieces:

Concatenate("SELECT A FROM abc WHERE Pal ="
& [Pal]
& " AND custcode ="
& [custcode]
& " AND boxnr ='
& [boxnr]
& "'")

or, putting it all on one line,

Concatenate("SELECT A FROM abc WHERE Pal =" & [Pal] & " AND custcode
=" & [custcode] & " AND boxnr =' & [boxnr] & "'")

If custcode is a Text field you'll need quotes around the criterion,
just like boxnr; if boxnr is numeric, then leave off the ' and the
"'".

 

[Duane Hookom]

John, you missed a double-quote after boxnr='.
Concatenate("SELECT A FROM abc WHERE Pal =" & [Pal] & " AND custcode = " &
[custcode] & " AND boxnr ='" & [boxnr] & "'")

--
Duane Hookom
MS Access MVP

B: Concatenate("SELECT A FROM abc WHERE Pal =" & [Pal]
and custcode =" & [custcode] and boxnr =' & [boxnr])

Can anyone tell me what I did wrongly?

As often happens in this circumstance... you messed up your quotes.

When you're building a SQL string you need to a) determine what you
want the final string to look like, and b) build it up from string
constants delimited by " marks and from variables. In this case if
[Pal] is equal to 3, [custcode] is equal to 845, and [boxnr] is equal
to BK333, you want the SQL string to read (after it's concatenated)

SELECT A FROM abc WHERE Pal = 3 AND custcode = 845 AND boxnr = 'BK333'

So build it up in pieces:

Concatenate("SELECT A FROM abc WHERE Pal ="
& [Pal]
& " AND custcode ="
& [custcode]
& " AND boxnr ='
& [boxnr]
& "'")

or, putting it all on one line,

Concatenate("SELECT A FROM abc WHERE Pal =" & [Pal] & " AND custcode
=" & [custcode] & " AND boxnr =' & [boxnr] & "'")

If custcode is a Text field you'll need quotes around the criterion,
just like boxnr; if boxnr is numeric, then leave off the ' and the
"'".