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

PeterBaileyUkAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PeterBaileyUkAuthor Commented:
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

0
Dale FyeCommented:
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

0
Dale FyeCommented:
believe the "missing operator" is a " which should be just inside the ), right before the HAVING clause
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

PeterBaileyUkAuthor Commented:
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'
0
Dale FyeCommented:
OK, lets backup a bit.  In your original query, which works, you have:

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

Note that in this query, the DConcat function has three arguments, but in the second example you posted (built via code), the DConcat function only has two arguments.  And I inadvertently tried to make the 2nd code work without paying attention close enough attention to the first.

So, lets backup.  What are you actually trying to achieve?  I cannot imagine you having more than one [YamahaModel] for a given VIN # (VIN's are supposed to be unique).  Do you really want to know, for every [YamahaModel], what VINs are on-hand?

BTW, generally, the DConcat function is used to concatenate multiple records from some other table (the many side of a one-to-many relationship).
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PeterBaileyUkAuthor Commented:
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.
0
PeterBaileyUkAuthor Commented:
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 & "'"
0
Dale FyeCommented:
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.
0
PeterBaileyUkAuthor Commented:
ok all up and running thank you, you spotted the missing parameter.
0
Dale FyeCommented:
Glad to help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.