SQL Quotes help

R

Robin

Hello,

I'm trying to write the WHERE part of a multi-part SQL statement IN VBA and
the quotes within the DMin function are driving me mad! I have spent hours
to no avail!

I have searched this discussion group and cannot find a single example of a
Domain Aggregate function within a VBA string SQL statement.

The following is from the Query design SQL view which is what I want, but
formatted as a string variable in VBA. (I added the stSQLw1= and
opening/closing quotes.)

stSQLw1 =
"WHERE
(((ClientAssignment.CASequence)=DMin("[CASequence]","ClientAssignment","CAComplete = False AND "& _
"[ClientAssignment]![ClientID] = """ & [ClientEngagement]![ClientID] & """
AND "&_
"[ClientAssignment]![EngagementID] = """ & [ClientEngagement]![EngagementID]
& """ AND " & _
"[ClientAssignment]![EngagementYr] = """ & [ClientEngagement]![EngagementYr]
& """"))) "

Any help would be appreciated.
Thank you,
Robin
 
D

Douglas J. Steele

What are the data types of the three fields in question (ClientID,
EngagementID and EngagementYr)? Quotes are only used with Text fields. If
the fields are numeric, change the three double quotes to a single double
quote.
 
S

Stuart McCall

Robin said:
Hello,

I'm trying to write the WHERE part of a multi-part SQL statement IN VBA
and
the quotes within the DMin function are driving me mad! I have spent
hours
to no avail!

I have searched this discussion group and cannot find a single example of
a
Domain Aggregate function within a VBA string SQL statement.

The following is from the Query design SQL view which is what I want, but
formatted as a string variable in VBA. (I added the stSQLw1= and
opening/closing quotes.)

stSQLw1 =
"WHERE
(((ClientAssignment.CASequence)=DMin("[CASequence]","ClientAssignment","CAComplete
= False AND "& _
"[ClientAssignment]![ClientID] = """ & [ClientEngagement]![ClientID] & """
AND "&_
"[ClientAssignment]![EngagementID] = """ &
[ClientEngagement]![EngagementID]
& """ AND " & _
"[ClientAssignment]![EngagementYr] = """ &
[ClientEngagement]![EngagementYr]
& """"))) "

Any help would be appreciated.
Thank you,
Robin
Click to expand...

I have an add-in specially for this purpose. It will lay out the SQL in VBA
code, then copy it to the clipboard, from where you can paste it where it
needs to be. Give it a try if you want:

http://www.smccall.demon.co.uk/Downloads.htm#SQLFormat

(you still need to follow Doug's advice re data types and quotes)
 
R

Robin

Daniel,

It works! What a great tool!
Thank you for that link....and thank you Allen!

Robin

Daniel Pineault said:
Been there, done that!

Check this out! Trust me, simply follow the instructions (2 mins) and you
will never have to worry about this problem again. Thank you Allen!

http://allenbrowne.com/ser-71.html
--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.



Robin said:
Hello,

I'm trying to write the WHERE part of a multi-part SQL statement IN VBA and
the quotes within the DMin function are driving me mad! I have spent hours
to no avail!

I have searched this discussion group and cannot find a single example of a
Domain Aggregate function within a VBA string SQL statement.

The following is from the Query design SQL view which is what I want, but
formatted as a string variable in VBA. (I added the stSQLw1= and
opening/closing quotes.)

stSQLw1 =
"WHERE
(((ClientAssignment.CASequence)=DMin("[CASequence]","ClientAssignment","CAComplete = False AND "& _
"[ClientAssignment]![ClientID] = """ & [ClientEngagement]![ClientID] & """
AND "&_
"[ClientAssignment]![EngagementID] = """ & [ClientEngagement]![EngagementID]
& """ AND " & _
"[ClientAssignment]![EngagementYr] = """ & [ClientEngagement]![EngagementYr]
& """"))) "

Any help would be appreciated.
Thank you,
Robin
Click to expand...
Click to expand...
 
R

Robin

Thanks Stuart,

I'll give that a try. That format is much easier to troubleshoot.

Stuart McCall said:
Robin said:
Hello,

I'm trying to write the WHERE part of a multi-part SQL statement IN VBA
and
the quotes within the DMin function are driving me mad! I have spent
hours
to no avail!

I have searched this discussion group and cannot find a single example of
a
Domain Aggregate function within a VBA string SQL statement.

The following is from the Query design SQL view which is what I want, but
formatted as a string variable in VBA. (I added the stSQLw1= and
opening/closing quotes.)

stSQLw1 =
"WHERE
(((ClientAssignment.CASequence)=DMin("[CASequence]","ClientAssignment","CAComplete
= False AND "& _
"[ClientAssignment]![ClientID] = """ & [ClientEngagement]![ClientID] & """
AND "&_
"[ClientAssignment]![EngagementID] = """ &
[ClientEngagement]![EngagementID]
& """ AND " & _
"[ClientAssignment]![EngagementYr] = """ &
[ClientEngagement]![EngagementYr]
& """"))) "

Any help would be appreciated.
Thank you,
Robin
Click to expand...

I have an add-in specially for this purpose. It will lay out the SQL in VBA
code, then copy it to the clipboard, from where you can paste it where it
needs to be. Give it a try if you want:

http://www.smccall.demon.co.uk/Downloads.htm#SQLFormat

(you still need to follow Doug's advice re data types and quotes)


.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

INSERT INTO in a One-toMany relationship 0
SQL SELECT WHERE Problem 1
Repost: SQL SELECT WHERE Problem 3
Updatable Query (trying to edit records) 7
VBA Export to PDF 0
DSum with multiple criteria 4
Repost:DSum w/multiple criteria (Invalid Syntax) 7
SQL for list manipulation (a mess) 10

Top