Quotation Marks With DCount and Multiple Parameters

[kms23518]

Hello,

I created a query in Query Builder which contains a DCount with multiple parameters and it runs as it should. I am trying to convert it to VBA, but my inability to put in the quotations marks correctly is frustrating me terribly.

Here is the SQL version from Query Builder:

UPDATE [Daily Status Update Table] SET [Daily Status Update Table].NumberOfChases = DCount("[ChaseOtherID5]","[Chases_View_ALL - TX_Mbr 9 Digit]","[ChaseOtherID5] = 'U - Initial Contact' AND [ChaseStatus] = 'A'"), [Daily Status Update Table].ChaseStatus = "A", [Daily Status Update Table].NewStatus = "A", [Daily Status Update Table].ChaseAssignment2 = "Unscheduled"
WHERE ((([Daily Status Update Table].ChaseOtherID5)="U - Initial Contact"));

Any suggestions would be appreciated.

 

[spikepl]

My first suggestion would be for you to choose a naming convention not inluding spaces, or nonalphanumeric characterts for your object and field names, and then applying it consistently. http://www.access-programmers.co.uk/forums/showthread.php?t=225837 Your current "convention" is a mess, which sets you up for eternal PITA

 

[kms23518]

I know what you are saying, but this is not a mess that I made, I am simply tasked with populating a few fields in an existing mess.

 

[WickyScratch]

I generally split my SQL statements into separate strings when converting them to VBA. I have the declarations in a module as global variables.

For this, I would do:
strUPDATE = "UPDATE [Daily Status Update Table] "
strSET = "SET [Daily Status Update Table].NumberOfChases = " & _
DCount("[ChaseOtherID5]","[Chases_View_ALL - TX_Mbr 9 Digit]","[ChaseOtherID5] = 'U - Initial Contact' AND [ChaseStatus] = 'A'") & _
", [Daily Status Update Table].ChaseStatus = 'A', [Daily Status Update Table].NewStatus = 'A', [Daily Status Update Table].ChaseAssignment2 = 'Unscheduled'
strWHERE = "WHERE ((([Daily Status Update Table].ChaseOtherID5)='U - Initial Contact'));"
strSQL = strUPDATE & strSET & strWHERE
DoCmd.RunSQL strSQL

With this method, you have the DCount outside of the string, so you don't need to worry about the Quotation Marks ... however, inside the SQL statement, it doesn't like quotation marks so those need to be changed to apostrophes.

If you absolutely had to have quotation marks within the statement (such as updating a text field to have a quotation mark in it), use "Chr(34)" to do a Quotation mark.