I'm not sure about this error message, what does it mean

[ViperD]

I am running a sql statement:

DoCmd.RunSQL ("Select Claim_Type,Primary_Pymt from Tests where PatientSS#=" & Form![PatientSS#])

When I run the statement I get a "Run-time error '3075' Syntax error in date in query expression 'PatientSS#=123456789'.

Why would I get this error message? The sql statement should pull a date.

 

[vbajock]

Put Patient# in brackets [Patient#]. # is a reserved character, used to deliminate date values.

 

[ViperD]

When I do that it says that the RunSQL action requires an arguement consisting on a sql statement.

 

[vbajock]

Try
DoCmd.RunSQL ("Select Claim_Type,Primary_Pymt from Tests where [PatientSS#]= '" & Forms!whatevertheformname is[PatientSS#] &+"'&quot

 

[jebry]

Hi!

Just rename the field and the text box to eliminate the #. Even if you get this to work this time the # will be a pain for you anytime you try to do something with this field.

hth


Jeff Bridgham
bridgham@purdue.edu

 

[danvlas]

RunSQL is used to run action queries (append, update, delete, make table).

Not for Select queries...



Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[ViperD]

What should I use for select queries then?

Thanks all for the help.

 

[vbajock]

My bad! I was concentrating so much on the syntax I didn't see the invalid command.

It depends on what you are trying to do. If you are just trying to pop records on the screen use Docmd.openquery and embed the Forms!frmWhatever![PatientSS#] in criteria line of the saved query.

If you are trying to loop through a recordset in code use
the ADO .open or .execute method or the DAO opendynaset stuff.

 

[ViperD]

What I want to do is find the claim_type and payment_amount for the patient. If the claim is of a certain type I want to divide the payment in half and put that value into a field on the form. I'm just trying to figure out how to code the sql statement.

 

[vbajock]

If you base your form on the query outlined abovc, you could use an IIF statement in the payment_amount text box:

=IIF(me!claim_type ="X", [payment_amount ]/.5,[payment_amount ])

Buy the way, 90% of my customers are healthcare.

http://www.viningcomputers.com