asked on

access sql syntax with dConcat

ok this manual query works:

SELECT foundresults.Vin, foundresults.YamahaModel, DConcat("[YamahaModel]","[foundresults]","[vin]=""10F000109""") AS Expr1
FROM foundresults
WHERE (((foundresults.Vin)="10F000109"));

Open in new window

I need to create a dynamic query but having trouble with the syntax (quotes)

help would be appreciated.

vba code:

        strSQL = "SELECT foundresults.Vin, DConcat(""[YamahaModel]"",""foundresults.[Vin] = '" & VinIn & "') AS Expr1" _
        & " FROM foundresults " _
        & " GROUP BY foundresults.Vin, DConcat(""[YamahaModel]"",""foundresults.[Vin] = '" & VinIn & "')" _
        & " HAVING (((foundresults.Vin)='" & VinIn & "'));"

   

   Debug.Print VinIn
   
  Debug.Print "SQL for " & strQuery & ": " & strSQL

Open in new window

PeterBaileyUk

ASKER

ok ive made a small amendment but cannot spot the error missing operator.

this is what whas generated:

SELECT qryvinmodelgroups.Vin, DConcat("[YamahaModel]","foundresults.[Vin] = '_10F0103640') AS Expr1 FROM qryvinmodelgroups GROUP BY qryvinmodelgroups.Vin, DConcat("[YamahaModel]","foundresults.[Vin] = '_10F0103640') HAVING (((qryvinmodelgroups.Vin)='_10F0103640'));

this is how it was generated

any ideas?

  strSQL = "SELECT qryvinmodelgroups.Vin, DConcat(""[YamahaModel]"",""foundresults.[Vin] = '" & VinIn & "')" & " AS Expr1" _
        & " FROM qryvinmodelgroups " _
        & " GROUP BY qryvinmodelgroups.Vin, DConcat(""[YamahaModel]"",""foundresults.[Vin] = '" & VinIn & "')" _
        & " HAVING (((qryvinmodelgroups.Vin)='" & VinIn & "'));"

Open in new window

Dale Fye

Try:

strSQL = "SELECT foundresults.Vin" _
             & ", DConcat(" & chr$(34) & "[YamahaModel]" & chr$(34) & ", " _
                                       & chr$(34) & "foundresults.[Vin] = '" & VinIn & "'" & chr$(34) & ") AS Expr1" _
        & " FROM foundresults" _
        & " GROUP BY foundresults.Vin" _
             & ", DConcat(" & chr$(34) & "[YamahaModel]" & chr$(34) & ", " _
                                       & chr$(34) & "foundresults.[Vin] = '" & VinIn & "'" & chr$(34) & ")" _
        & " HAVING foundresults.Vin= '" & VinIn & "'"

Open in new window

Dale Fye

believe the "missing operator" is a " which should be just inside the ), right before the HAVING clause

PeterBaileyUk

ASKER

Its saying data type mismatch but not sure why.

SELECT foundresults.Vin, DConcat("[YamahaModel]", "foundresults.[Vin] = '_10F0103640'") AS Expr1 FROM foundresults GROUP BY foundresults.Vin, DConcat("[YamahaModel]", "foundresults.[Vin] = '_10F0103640'") HAVING foundresults.Vin= '_10F0103640'

ASKER CERTIFIED SOLUTION

Dale Fye

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PeterBaileyUk

ASKER

ok another process has determined that a vin could be 1 or many variant. hence sr125, sr125e. and I have a table with the vin and model here is an entry from table foundresults:

Vin        YamahaModel      Process reason      Id
3HE058064       FZR600               29
3HE058064      FZR600H               30

I wanted to create an entry in a new table
Vin YamahaModel
3HE058064 FZR600, FZR600H

and to do this for each vin

Thats interesting because:
heres the output:

Vin        YamahaModel        Expr1
10F000109        SR125        SR125, SR125SE
10F000109      SR125SE        SR125, SR125SE

from this:

SELECT foundresults.Vin, foundresults.YamahaModel, DConcat("[YamahaModel]","[foundresults]","[vin]=""10F000109""") AS Expr1
FROM foundresults
WHERE (((foundresults.Vin)="10F000109"));

and yes I didnt spot that either with the parameter.

PeterBaileyUk

ASKER

ok i think its this:

strSQL = "SELECT foundresults.Vin" _
& ", DConcat(" & Chr$(34) & "[YamahaModel]" & Chr$(34) & ", ""[foundresults]"", " _
& Chr$(34) & "foundresults.[Vin] = '" & VinIn & "'" & Chr$(34) & ") AS Expr1" _
& " FROM foundresults" _
& " GROUP BY foundresults.Vin" _
& ", DConcat(" & Chr$(34) & "[YamahaModel]" & Chr$(34) & ", ""[foundresults]""," _
& Chr$(34) & "foundresults.[Vin] = '" & VinIn & "'" & Chr$(34) & ")" _
& " HAVING foundresults.Vin= '" & VinIn & "'"

Dale Fye

Did that work, looks like it should? You don't really need to refer to foundresults.vin in the DConcat criteria argument, since you are specifically addressing the [foundresults] table. Also, I don't like using the double and triple quotes in my queries, so I have reformated the 2nd argument in the DConcat in the SQL below.

strSQL = "SELECT foundresults.Vin" _
& ", DConcat(" & Chr$(34) & "[YamahaModel]" & Chr$(34) & ", " _
& chr$(34) & "[foundresults]" & chr$(34) & ", " _
& Chr$(34) & "[Vin] = '" & VinIn & "'" & Chr$(34) & ") AS Expr1" _
& " FROM foundresults" _
& " GROUP BY foundresults.Vin" _
& ", DConcat(" & Chr$(34) & "[YamahaModel]" & Chr$(34) & ", " _
& chr$(34) & "[foundresults]" & chr$(34) & "," _
& Chr$(34) & "[Vin] = '" & VinIn & "'" & Chr$(34) & ")" _
& " HAVING foundresults.Vin= '" & VinIn & "'"

You also mentioned in your previous (2nd above) that: "and to do this for each vin".
If that is the case, why are you including the "HAVING" clause? Just leave that out to get it for all VINs.

PeterBaileyUk

ASKER

ok all up and running thank you, you spotted the missing parameter.

Dale Fye

Glad to help.