DCount Returning a Syntax Error 1

[Sydney1]

Hi,

I'm trying to use dcount to see if the 2 fields combined are unique in a query. The user doesn't like the error message that is returned from SQL Server (Access frontend and SQL Server backend) using the unique index for the 2 fields. Both fields are char (text in Access) in Sql Server.

I currently have the following code

If DCount("*", "qryInvestorList", _
 "InvestorID='"& Forms!frmInvestorMatrixCopy!InvestorID & "' And "MSP_Category='" & Forms!frmInvestorMatrixCopy!MSP_Category &"') <> 0 Then

thanks in advance for your help.

Sydney

 

[TomCologne]

Apostrophe is missing here:

Forms!frmInvestorMatrixCopy!MSP_Category &"'"[/color red]

TomCologne

 

[Sydney1]

Thanks for the reply and help.

I added the apostrophe at the end after the "', but am still getting a syntax error.

Again, thanks for your help.

Sydney

 

[TomCologne]

Do you get the same error when you replace

"*" with "[Fieldxyz]"?

TomCologne

 

[Sydney1]

Yes. The error states "Compile error. Expected expression" on both of them. It did state syntax error on the way I had it before I submitted the code here. I then changed it to what I submitted here and got the above error right from the start.


Thanks again for your help.

 

[TomCologne]

Sydney,

You're very welcome. I went through you code again:

If DCount("*", "qryInvestorList", _
 "[InvestorID]='" & [Forms]![frmInvestorMatrixCopy]![InvestorID] & "' And "[MSP_Category]='" & [Forms]![frmInvestorMatrixCopy]![MSP_Category] & "'") <> 0 Then

I believe to have caught any possible cause for this syntax error. Wanna give it a shot?

TomCologne

 

[Sydney1]

Tom,

Tried the code and am now getting a syntax error instead of the Expected expression error that I was receiving.

Thanks,

Sydney

 

[TomCologne]

@#$$^*&,

still missed one! The red apostrophe needs to go.
Sorry about that, Sydney.

If DCount("*", "qryInvestorList", _
 "[InvestorID]='" & [Forms]![frmInvestorMatrixCopy]![InvestorID] & "' And [COLOR=red][b]"[/b][/color red][MSP_Category]='" & [Forms]![frmInvestorMatrixCopy]![MSP_Category] & "'") <> 0 Then

Hope this will finally do it,

TomCologne

 

[Sydney1]

Tom,

That works like a charm! I still haven't grasped the single and double quotes that have to be used in text and combining fields. Someday I'll get it.

Again, Thanks so much for your help. I really apreciate the time and effort you've taken to help me.

Sydney

 

[TomCologne]

My pleasure, Sydney!

Syntax problems can be a major pain down south. I sure am glad that it's finally working.

TomCologne

 

[PHV]

I still haven't grasped the single and double quotes that have to be used in text and combining fields.
Use double quotes for VBA code
Use single quotes for SQL code

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886