Compile Error in query

[psyduck1085]

HI All,

I have some issues regarding to compile error on my VBA.

Please read the below error code:

Compile error. in query expression Conc("T24 Reference","Counterparty",[Counterparty],"XX4-Testing")

Can somebody tell me what wrong with it???

I had been trying to do tools>preference and try to find out any "Missing" but can't find.... even try to uncheck and recheck back also cant solve the error....

Please help!

 

[namliam]

conc is not any function I know.... what is it you are trying to do in what language/database?

 

[psyduck1085]

Hi Namliam,

It was same issues like yesterday post.... I add a new fuction in ms. access....

and come with this error. Below are my VBA


Public Function Conc(Fieldx, Identity, Value, Source) As Variant
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim SQL As String
Dim vFld As Variant

Set cnn = CurrentProject.Connection
Set rs = New ADODB.Recordset
vFld = Null

SQL = "SELECT [" & Fieldx & "] as Fld" & _
" FROM [" & Source & "]" & _
" WHERE [" & Identity & "]=" & Value

' open recordset.
rs.Open SQL, cnn, adOpenForwardOnly, adLockReadOnly

' concatenate the field.
Do While Not rs.EOF
If Not IsNull(rs!Fld) Then
vFld = vFld & ", " & rs!Fld
End If
rs.MoveNext
Loop
' remove leading comma and space.
vFld = Mid(vFld, 3)

Set cnn = Nothing
Set rs = Nothing

' return concatenated string.
Conc = vFld
End Function

 

[namliam]

What is the content of your [counterparty] field?

I can imagine this throwing an error if the field is anything but numeric... and/or contains a NULL value.

 

[psyduck1085]

Hi Namliam,

In the C/P field is contain name....example... Kinokuniya...MPH... and etc etc... for T24 reference...it contain reference like TD1234567 or DC564789...

Anything wrong with the query?

 

[spikepl]

This is the price you pay for having spaces in field/object names.

Update: ... and other non-alphanumerics

 

[psyduck1085]

HI Spikepl...

Thank for your advice... but then in my counterparty field... there are no space... or any non-alphanumerics.....? so any problems?

 

[spikepl]

Hmmm let me think ... then it is not your counterparty field where the problem is

 

[psyduck1085]

Coz what I want is simple...

1 a TO 1 abc
1 b 3 a
1 c
3 a

so I add a new fuction conc in ms access and 1 is my counterparty (example kinokuniya) and abc (DC1234567) is my reference.

Any issues??? and solution! very desperate!

 

[pr2-eugin]

Did you try Debugging the CODE from the immediate window? Is there any reason why you have used ADODB library instead of DAO?

To add to spikepl's list - Value a very bad variable/Field name.

 

[psyduck1085]

pr2,

I trying to do so and i get a yellow arrow into
rs.Open SQL, cnn, adOpenForwardOnly, adLockReadOnly

I'm not sure what wrong with the code????

 

[pr2-eugin]

I am repeating what namliam said before

What is the content of your [counterparty] field?

I can imagine this throwing an error if the field is anything but numeric... and/or contains a NULL value.

So what is [counterparty] a Numeric field (pure numbers)? Text field (alphanumeric values)?

So when the error occurs, you will be presented with the Error description; What is that? if you hover over the highlighted code, you can see the value of some of the string. Is it coming out properly?

How about declaring the Field names with types? Like Fieldx As String etc?

 

[spikepl]

Forget the content of the counterparty field. Read my post again - carefully this time.

 

[namliam]

the content of the counterparty field IS important, read my post again



Conc("T24 Reference","Counterparty",[Counterparty],"XX4-Testing") << Sending the VALUE of CounterParty into the function

Public Function Conc(Fieldx, Identity, Value, Source) As Variant << CounterParty here becomes Value
Since value is part of the query's where clause, the way it is build it needs to be numeric... othewize it becomes a parameter query which isnt possible in this way.

SQL = "SELECT [" & Fieldx & "] as Fld" & _
      " FROM [" & Source & "]" & _
      " WHERE [" & Identity & "]=" & Value

Thus.... since the CounterParty field is a text field (i.e. not a numeric field)
that contains

In the C/P field is contain name....example... Kinokuniya...MPH...

To make the query work you need to enclose the Value by quotes

SQL = "SELECT [" & Fieldx & "] as Fld" & _
      " FROM [" & Source & "]" & _
      " WHERE [" & Identity & "]='" & Value & "'"

 

[psyduck1085]

Hi Namliam,

I get another syntax error after I put "'" in the value....

"Syntax error in string in query expression"
"[Counterparty]=KINOKUNIYA"

 

[psyduck1085]

Hi Namliam and ALL,

Thank for your help!!... everything is good!...

Thank for everyone help!!!!