Run-time error 3075 in QDF 2

[soloracer]

Hi folks,

Could someone point out what I'm doing wrong with this query? The basics ran OK in the query grid and it compiles but I get the error when I try to run it within VBA ...

Dim qdf2 As DAO.QueryDef, strq As String

strq = strq & " SELECT TOP 6 tblResults.pos, tblResults.membername "
strq = strq & " FROM (tblRace INNER JOIN tblSeries ON [tblRace].[seriesID] = [tblSeries].[seriesID]) INNER JOIN tblResults ON [tblRace].[raceauto] = [tblResults].[raceID] "
strq = strq & " WHERE ((([tblSeries].[seriesID]) = 45) And (([tblResults].[membername]) = " & strname & " ))"
strq = strq & " ORDER BY [tblResults].[pos];"

Set qdf2 = db.CreateQueryDef("qrywall2", strq)

(strname is a string variable obtained earlier in the code)

The full error code text is
Run-time error '3075'
Syntax error 'missing operator' in query expression
'((([tblSeries].[seriesID]) = 45) And (([tblResults].[membername]) = Fred Bloggs))'.

(strname = Fred Bloggs is correct)

When I get this to work, I plan to replace the 'test' value of TOP and seriesID with variable values and then
DSUM the results to get the total value of pos for the Top N values -
- such as intval = DSum(pos, "qrywall2") ... though I suspect there is a much more effetive way to achieve all this in one query.

Grateful for your thoughts and best regards to all

 

[combo]

The string in condition should be between quotation marks:
strq = strq & " WHERE ((([tblSeries].[seriesID]) = 45) And (([tblResults].[membername]) = " & Chr(34) & strname & Chr(34) & " ))"

combo

 

[soloracer]

Spot on! Thank you combo. I'll pin that one on the wall. Chr(34) is double quote ... somehow I was expecting a single quote somewhere. Never been able to find a really simple description of what to use where

My use of DSum to get the total value of 'pos' works too though I still think that it should be possible (and faster) to combine the two elements into one query ...

Can I ask if you foresee any problems replacing the TOP value (6 was my test case) and [seriesID] with variables?

Thanks again combo. Can I award points somewhere?

 

[combo]

No problem with single quote, you can add it to the string:
[pre]...And (([tblResults].[membername]) = '" & ...[/pre]
I just put double quotes because access does so: in SQL view of query with string condition.
Also, you can combine variables with strings to build sql string. After Debug.Print one can see in the immediate window if the sql string is ok.

At the bottom-rignt of others posts you can see a purple star with Great post!. You can award as many persons as you like in a thread, but with single star for each. A star stays in the last post you awarded.

combo

 

[soloracer]

Great Post, understood.

In the query, I used test data for TOP (=6) and [seriesID] (=45). I have dynamic values for TOP and [seriesID).Should I use
this CHR(34) approach to replace the test values with these values which may change each time the report is run?

ie. for [seriesID] I have a combobox value from the driving form and for TOP, a value calculated in the report header.

Thanks for any additional insight and best regards

solosailor

 

[Andrzejek]

No [tt]CHR(34)[/tt] needed for your TOP or seriesID since they are numbers.

Consider;
[tt]
Dim [blue]intMyTopNo[/blue] As integer

[blue]intMyTopNo[/blue] = 6

strq = strq & " SELECT TOP " & [blue]intMyTopNo[/blue] & " tblResults.pos, tblResults.membername "
strq = strq & " FROM (tblRace INNER JOIN ...
[/tt]


---- Andy

There is a great need for a sarcasm font.

 

[soloracer]

My thanks to Andy too. For combo and Andy, here's the final outcome which works just fine. Still not sure that using that DSUM but hey, it works well enough! And until I found it on this forum, I never knew about debug.print and that's proved really useful.

Dim strname As String
strname = Trim(Split(([txthelm]), ".")(0))

db.QueryDefs.Delete "qrywall2"
Dim qdf2 As DAO.QueryDef, strq As String

strq = strq & " SELECT TOP " & Int((Forms!wallchart!txttotalraces - Forms!wallchart!txtabandoned) / 2) & " tblResults.pos, tblResults.membername "
strq = strq & " FROM (tblRace INNER JOIN tblSeries ON [tblRace].[seriesID] = [tblSeries].[seriesID]) INNER JOIN tblResults ON [tblRace].[raceauto] = [tblResults].[raceID] "
strq = strq & " WHERE ((([tblSeries].[seriesID]) = " & Forms!wallchart!Combo3 & ") And (([tblResults].[membername]) = " & Chr(34) & strname & Chr(34) & " ))"
strq = strq & " ORDER BY [tblResults].[pos];"

Debug.Print strq

Set qdf2 = db.CreateQueryDef("qrywall2", strq)

Dim inttop As Integer
inttop = DSum("pos", "qrywall2")

qdf2.Close

 

[Andrzejek]

I am glad it worked for you.
And welcome to TT (Tek Tips) It is a great forum with a lot of knowledgeable people ready to lend a hand.


---- Andy

There is a great need for a sarcasm font.