SQL Quotes help

  • Thread starter Thread starter Robin
  • Start date Start date
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
 
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.
 
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)
 
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...
 
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...
 
Back
Top