Query Help

[Guest]

I have a table1 in which the information is like this:

[FIELD1] [FIELD2]
A 1
A 6
A 42
B 7
B 10

I want to display the information in a continous form1 like this:

[FIELD] [FIELDZ]
A 1, 6, 42
B 7, 10

I have tried a concatenate function for FIELDZ in the control source. -
=Concatenate("SELECT FIELD2 FROM TABLE1 WHERE FIELD1=Forms!Form1![FIELD]").

When ever i try to put a where fucntion in that isn't static eg field1=A, i
get an error.

Is my statement wrong, or am i going about this the wrong way.

TIA

 

[Douglas J Steele]

Try:

=Concatenate("SELECT FIELD2 FROM TABLE1 WHERE FIELD1= '" &
Forms!Form1![FIELD] & "'")

or, if Field1 might contain single quotes:

=Concatenate("SELECT FIELD2 FROM TABLE1 WHERE FIELD1= " Chr$(34) &
Forms!Form1![FIELD] &Chr$(34))

Note that the first one is

=Concatenate("SELECT FIELD2 FROM TABLE1 WHERE FIELD1= ' " &
Forms!Form1![FIELD] & " ' " )

 

[Guest]

Thanks Douglas,

The first gives a name? error and the second one i get a syntax error, which
i did try to resolve, to no avail.

Any other ideas

Thanks

Try:

=Concatenate("SELECT FIELD2 FROM TABLE1 WHERE FIELD1= '" &
Forms!Form1![FIELD] & "'")

or, if Field1 might contain single quotes:

=Concatenate("SELECT FIELD2 FROM TABLE1 WHERE FIELD1= " Chr$(34) &
Forms!Form1![FIELD] &Chr$(34))

Note that the first one is

=Concatenate("SELECT FIELD2 FROM TABLE1 WHERE FIELD1= ' " &
Forms!Form1![FIELD] & " ' " )

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I have a table1 in which the information is like this:

[FIELD1] [FIELD2]
A 1
A 6
A 42
B 7
B 10

I want to display the information in a continous form1 like this:

[FIELD] [FIELDZ]
A 1, 6, 42
B 7, 10

I have tried a concatenate function for FIELDZ in the control source. -
=Concatenate("SELECT FIELD2 FROM TABLE1 WHERE FIELD1=Forms!Form1![FIELD]").

When ever i try to put a where fucntion in that isn't static eg field1=A, i
get an error.

Is my statement wrong, or am i going about this the wrong way.

TIA

 

[Guest]

Sorry, i had the first one mistyped, i now get a data type mismatch pop up!

Try:

=Concatenate("SELECT FIELD2 FROM TABLE1 WHERE FIELD1= '" &
Forms!Form1![FIELD] & "'")

or, if Field1 might contain single quotes:

=Concatenate("SELECT FIELD2 FROM TABLE1 WHERE FIELD1= " Chr$(34) &
Forms!Form1![FIELD] &Chr$(34))

Note that the first one is

=Concatenate("SELECT FIELD2 FROM TABLE1 WHERE FIELD1= ' " &
Forms!Form1![FIELD] & " ' " )

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I have a table1 in which the information is like this:

[FIELD1] [FIELD2]
A 1
A 6
A 42
B 7
B 10

I want to display the information in a continous form1 like this:

[FIELD] [FIELDZ]
A 1, 6, 42
B 7, 10

I have tried a concatenate function for FIELDZ in the control source. -
=Concatenate("SELECT FIELD2 FROM TABLE1 WHERE FIELD1=Forms!Form1![FIELD]").

When ever i try to put a where fucntion in that isn't static eg field1=A, i
get an error.

Is my statement wrong, or am i going about this the wrong way.

TIA

 

[Douglas J Steele]

Can you determine exactly what the SQL statement being passed to the
Concatenate function is?

Presumably it needs to be something like SELECT FIELD2 FROM TABLE1 WHERE
FIELD1= 'A'

Once you know what SQL is being passed, try calling the Concatenate function
yourself, pass it that exact string.

Since there's no Concatenate function in Access, I'm assuming it's a
user-defined function in your application: without knowing anything about
it, it's essentially impossible for me to help.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Sorry, i had the first one mistyped, i now get a data type mismatch pop up!

Try:

=Concatenate("SELECT FIELD2 FROM TABLE1 WHERE FIELD1= '" &
Forms!Form1![FIELD] & "'")

or, if Field1 might contain single quotes:

=Concatenate("SELECT FIELD2 FROM TABLE1 WHERE FIELD1= " Chr$(34) &
Forms!Form1![FIELD] &Chr$(34))

Note that the first one is

=Concatenate("SELECT FIELD2 FROM TABLE1 WHERE FIELD1= ' " &
Forms!Form1![FIELD] & " ' " )

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I have a table1 in which the information is like this:

[FIELD1] [FIELD2]
A 1
A 6
A 42
B 7
B 10

I want to display the information in a continous form1 like this:

[FIELD] [FIELDZ]
A 1, 6, 42
B 7, 10

I have tried a concatenate function for FIELDZ in the control source. -
=Concatenate("SELECT FIELD2 FROM TABLE1 WHERE FIELD1=Forms!Form1![FIELD]").

When ever i try to put a where fucntion in that isn't static eg

field1=A,
i

get an error.

Is my statement wrong, or am i going about this the wrong way.

TIA