I have a query that I change the table in depending on the selection of the customer. I have done this before and it works fine except now I need to have a DMin statement and I keep getting an error on it.Heres the code - Set StageStsMM = db.QueryDefs("StageStsMM")
-
StageStsMM.SQL = "SELECT " & vst & ".SASS_number, Params.Number_stages, " & vst & ".Shoot_date AS shootdate, " & vst & ".ShooterNumber, " & vst & ".Class AS Class, Shooting_Class.Description, Shooting_Class.ScoringCheck, " & _
-
"" & vst & ".Time1 , " & vst & ".TTime1, " & vst & ".Misses1, " & vst & ".Penelties1, " & vst & ".Time2, " & vst & ".TTime2, " & vst & ".Misses2, " & vst & ".Penelties2, " & vst & ".Time3, " & vst & ".TTime3, " & _
-
"" & vst & ".Misses3, " & vst & ".Penelties3, " & vst & ".Time4, " & vst & ".TTime4, " & vst & ".Misses4, " & vst & ".Penelties4, " & vst & ".Time5, " & vst & ".TTime5, " & vst & ".Misses5, " & vst & ".Penelties5, " & _
-
"" & vst & ".Time6, " & vst & ".TTime6, " & vst & ".Misses6, " & vst & ".Penelties6, " & vst & ".Time7, " & vst & ".TTime7, " & vst & ".Misses7, " & vst & ".Penelties7, " & vst & ".Time8, " & vst & ".TTime8, " & _
-
"" & vst & ".Misses8, " & vst & ".Penelties8, " & vst & ".Time9, " & vst & ".TTime9, " & vst & ".Misses9, " & vst & ".Penelties9, " & vst & ".Time10, " & vst & ".TTime10, " & vst & ".Misses10, " & vst & ".Penelties10, " & _
-
"" & vst & ".Time11, " & vst & ".TTime11, " & vst & ".Misses11, " & vst & ".Penelties11, " & vst & ".Time12, " & vst & ".TTime12, " & vst & ".Misses12, " & vst & ".Penelties12, " & vst & ".Time13, " & vst & ".TTime13, " & vst & ".Misses13, " & vst & ".Penelties13, " & vst & ".Time14, " & _
-
"" & vst & ".TTime14, " & vst & ".Misses14, " & vst & ".Penelties14, " & vst & ".Time15, " & vst & ".TTime15, " & vst & ".Misses15, " & vst & ".Penelties15, " & vst & ".Time16, " & vst & ".TTime16, " & vst & ".Misses16, " & _
-
"" & vst & ".Penelties16, " & vst & ".Time17, " & vst & ".TTime17, " & vst & ".Misses17, " & vst & ".Penelties17, " & vst & ".Time18, " & vst & ".TTime18, " & vst & ".Misses18, " & vst & ".Penelties18, " & vst & ".TotalTime, " & _
-
"Params.Shoot_date, Params.Club_name, Params.RegionalName, Master.Alias, Params.MisseTime, Params.PenaltiesTime, DMin("[tTime1]","[" & vst & "]","[tTime1] > 0 and class = '" & [Class] & "'") AS mTime1, " & _
-
"DMin("[tTime2]","[" & vst & "]","[tTime2] > 0 and class = '" & [Class] & "'") AS mTime2, DMin("[tTime3]","[" & vst & "]","[tTime3] > 0 and class = '" & [Class] & "'") AS mTime3, DMin("[tTime4]","[" & vst & "]","[tTime4] > 0 and class = '" & [Class] & "'") AS mTime4, " & _
-
"DMin("[tTime5]","[" & vst & "]","[tTime5] > 0 and class = '" & [Class] & "'") AS mTime5, DMin("[tTime6]","[" & vst & "]","[tTime6] > 0 and class = '" & [Class] & "'") AS mTime6, DMin("[tTime7]","[" & vst & "]","[tTime7] > 0 and class = '" & [Class] & "'") AS mTime7, " & _
-
"DMin("[tTime8]","[" & vst & "]","[tTime8] > 0 and class = '" & [Class] & "'") AS mTime8, DMin("[tTime9]","[" & vst & "]","[tTime9] > 0 and class = '" & [Class] & "'") AS mTime9, DMin("[tTime10]","[" & vst & "]","[tTime10] > 0 and class = '" & [Class] & "'") AS mTime10, " & _
-
"DMin("[tTime11]","[" & vst & "]","[tTime11] > 0 and class = '" & [Class] & "'") AS mTime11, DMin("[tTime12]","[" & vst & "]","[tTime12] > 0 and class = '" & [Class] & "'") AS mTime12, DMin("[tTime13]", " & _
-
"[" & vst & "]","[tTime13] > 0 and class = '" & [Class] & "'") AS mTime13, DMin("[tTime14]","[" & vst & "]","[tTime14] > 0 and class = '" & [Class] & "'") AS mTime14, DMin("[tTime15]","[" & vst & "]","[tTime15] > 0 and class = '" & [Class] & "'") AS mTime15, " & _
-
"DMin("[tTime16]","[" & vst & "]","[tTime16] > 0 and class = '" & [Class] & "'") AS mTime16, DMin("[tTime17]","[" & vst & "]","[tTime17] > 0 and class = '" & [Class] & "'") AS mTime17, DMin("[tTime18]","[" & vst & "]","[tTime18] > 0 and class = '" & [Class] & "'") AS mTime18, " & _
-
"" & vst & ".MSafety1, " & vst & ".MSafety2, " & vst & ".MSafety3, " & vst & ".MSafety4, " & vst & ".MSafety5, " & vst & ".MSafety6, " & vst & ".MSafety7, " & vst & ".MSafety8, " & vst & ".MSafety9, " & vst & ".MSafety10, " & vst & ".MSafety11, " & vst & ".MSafety12, " & _
-
"" & vst & ".MSafety13, " & vst & ".MSafety14, " & vst & ".MSafety15, " & vst & ".MSafety16, " & vst & ".MSafety17, " & vst & ".MSafety18, " & vst & ".Shootorder1, " & vst & ".Shootorder2, " & vst & ".Shootorder3, " & _
-
"" & vst & ".Shootorder4, " & vst & ".Shootorder5, " & vst & ".Shootorder6, " & vst & ".Shootorder7, " & vst & ".Shootorder8, " & vst & ".Shootorder9, " & vst & ".Shootorder10, " & vst & ".Shootorder11, " & vst & ".Shootorder12, " & _
-
"" & vst & ".Shootorder13, " & vst & ".Shootorder14, " & vst & ".Shootorder15, " & vst & ".Shootorder16, " & vst & ".Shootorder17, " & vst & ".Shootorder18, " & vst & ".MatchDQ FROM Params, Master INNER JOIN (Shooting_Class INNER JOIN " & vst & " ON Shooting_Class.Class = " & vst & ".Class) ON Master.SASS_number = " & vst & ".SASS_number " & _
-
"WHERE (((Shooting_Class.ScoringCheck)=False) AND ((" & vst & ".MatchDQ)=False))"
the first Dmin statement gives an error "Expected end of statement"
I've copied the sql statement directly from the query only changing the table name to the " & Vst & " which is replaced with the correct selected table name.
I have never used the Dmin statement before in a query build in VBA so can someone tell me how that statement should be?
Thanks again for your help.
5  867 
I think your table name is wrong in the DMin statements
It should be of the form -
DMin("[tTime2]", "vst", "Criteria")
-
where Criteria as whatever you want
If you type "DMin("[tTime2]", "vst", "Criteria") "
into the immediate window, you should see if this is giving the correct result.
Quite frankly, with a monster SQL statement like that, I tend to build it in the Query Builder and add additional statements until I have the complete SQL which I paste into my VBA
Phil
I'll give that a try and let you know.
Thanks
That didn't work either still get the same error. Any other suggestions.
NeoPa 32,564
Recognized Expert Moderator MVP
I'm surprised you still need to be told, at this stage after >400 posts, that posting VBA that creates SQL is less than bright.
Please read How to Debug SQL String first.
If you're still having problems then certainly feel free to share the SQL string that's failing.
As a further tip - Using Domain Aggregate functions within SQL is very rarely a recommended approach. That said, get your basics sorted out first before moving on to anything which would be classed as optimisation.
NeoPa 32,564
Recognized Expert Moderator MVP
Having looked at Phil's comment it seems clear you're using double-quotes (") within your intended SQL as well as in the VBA used to create that string. That's never going to work out for you. See Quotes (') and Double-Quotes (") - Where and When to use them.
@Tom.
I think of you as a very long-term member. Would I be right in thinking you've just recently moved towards SQL work after working in non-SQL based areas before now? If so, read the two linked threads as you should find them very helpful.
Sign in to post your reply or Sign up for a free account.
Similar topics | |
by: John Abel |
last post by:
I'm having a problem building pysqlite. I've downloaded the
sqlite_source.zip, and sqlitedll.zip and extracted them to a directory.
I've then extracted pysqlite-0.4.3. I've run the lib command, and made sure
that setup.py is pointing to the right place. However, when I run "python
setup.py build" I get the following error:
_sqlite.c...
|
by: Eskil |
last post by:
Hi
I have a form that supplies my query with information on two different
variables. The form uses a lookup to display a list of 5 different
customer types and 5 different types of meetings/activities in two
different combo boxes.
Now, the values of form variables go into a query upon which a report
is generated. By using those two...
|
by: Mossy |
last post by:
I am currently moving a table from a msSQL DB to a MySQL DB.
I am trying to build the bones of an insert statement by adding strings
to a select statement.
SELECT '(' + job_referenceno + ', ''' + job_title + ''', ''' +
job_category + ''', ''' + job_type + ''', ''' + job_location + ''', '''
+ job_duaration + ''', ' + job_salary + ', ''' +...
|
by: grabit |
last post by:
Hi Mary
this is the problem child (query)
<!---Query db for page info--->
<cfquery name="showtopics" datasource="parrots">
SELECT categories.catID, threads.threadID, threads.posttype, threads.topic, threads.topicID, threads.memberID, threads.username, threads.postdate, categories.category, T2.CountReplies
FROM (categories LEFT JOIN threads ON...
|
by: ashutoshvyas |
last post by:
I want to write such criteria in the query of microsoft ACCESS, which can give records with secondlast date?
table & fields............desired output
no-name-date..............name-date
1-A-07/15/07..............A-07/15/07
2-A-08/18/07..............B-04/12/07
3-B-04/12/07
4-B-08/18/07
5-A-07/03/07
| | |
by: manoj9849967222 |
last post by:
Hi All
I have a problem with query.
I have designed a simple query which sould give me itemwise total qty sales during a given period.
the fields in the query are
startdate, modelno,model,qty.
I have also designed a form where the user will give the period for example 1/1/2007 to 1/10/2007
|
by: viki1967 |
last post by:
Problem with query ASP and MySQL
Hi there.
I have this fields in the tbl (mysql) :
- ID (unique)
- user_name (text)
- Date (yyyy-mm-dd)
- Description (text - case A or B or C -)
|
by: cdhaynes |
last post by:
Hi, I am using unbound text boxes in the header of a form to allow the user to search on a number of fields. I am using VBA to build a query string based on these and it has been fine when comparing text fields on the main form but I have run in to trouble comparing date/time fields on a subform.
The subform is called Activity and I am comparing...
|
by: ajhayes |
last post by:
Hello everyone,
This is my first time posting here and I'm hoping someone can help me out. I'm a relative newbie to Access and am pretty much learning as I go along, so please bear with me.
Here is what I am trying to do. I'm building a simple database that will allow our engineering staff to enter in the items from the inventory warehouse...
|
by: kadishzm |
last post by:
Hi Everyone, I have recently been tasked with taking over a bunch of Access databases that I didn't create. My VBA skills are weak to say the best but I have a particular problem I am working on. What I want to do is use a form to identify multiple value that will pass through a query and generate excel documents for a split field. I already have...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
| | |
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it. ...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in...
| | |
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...
| |
