Select query, trying to hard code

[Trevor2007]

I am trying to hard code the following select query into a select case statement
ie (case1 <statment>, case 2 <statment>) but I getteing Compiler error: expected line number or label, or statement or end of statement on my select [VMS-CLT] line , with and without the pertentheses at the end of my where clause. here is what I have, the query functions proporly in the access query builder.

"SELECT [VMSU-CLT].Date, [VMSU-CLT].IDNumber, [VMSU-CLT].Time," _
& " [VMSU-CLT].CorrespondenceType, [VMSU-CLT].CallTakenBy, [VMSU-CLT].CallReferredTo," _
& " [VMSU-CLT].ReasonForReferral, [VMSU-CLT].Caller, [VMSU-CLT].VISTALastName," _
& " [VMSU-CLT].VISTAFirstName, [VMSU-CLT].VISTAMiddleName, [VMSU-CLT].State, [VMSU-CLT].ReasonForCall," _
& "[VMSU-CLT].[CalledVia800#], [VMSU-CLT].CallFollowUp, [VMSU-CLT].CallCompletdBy" FROM [VMSU-CLT]" _
& " WHERE ((([VMSU-CLT].Date)>=[forms]![Report query]![DateFrom] And ([VMSU-CLT].Date)<=[forms]![Repor query]![DateTo])))"

[puppydogbuddy]

I am trying to hard code the following select query into a select case statement
ie (case1 <statment>, case 2 <statment>) but I getteing Compiler error: expected line number or label, or statement or end of statement on my select [VMS-CLT] line , with and without the pertentheses at the end of my where clause. here is what I have, the query functions proporly in the access query builder.

"SELECT [VMSU-CLT].Date, [VMSU-CLT].IDNumber, [VMSU-CLT].Time," _
& " [VMSU-CLT].CorrespondenceType, [VMSU-CLT].CallTakenBy, [VMSU-CLT].CallReferredTo," _
& " [VMSU-CLT].ReasonForReferral, [VMSU-CLT].Caller, [VMSU-CLT].VISTALastName," _
& " [VMSU-CLT].VISTAFirstName, [VMSU-CLT].VISTAMiddleName, [VMSU-CLT].State, [VMSU-CLT].ReasonForCall," _
& "[VMSU-CLT].[CalledVia800#], [VMSU-CLT].CallFollowUp, [VMSU-CLT].CallCompletdBy" FROM [VMSU-CLT]" _
& " WHERE ((([VMSU-CLT].Date)>=[forms]![Report query]![DateFrom] And ([VMSU-CLT].Date)<=[forms]![Repor query]![DateTo])))"

AFAIK, you can’t directly use a select case statement in an Access Query, but you can create a user defined function that uses the select case statement, Here is what you do:

1. create/copy your select case function called VMSCriteria() (for example) to a standard module (it needs to be a public function to be used with a query).
2. You can then reference the function in the criteria row of the field in your query grid as follows: VMSCriteria()

[Trevor2007]

AFAIK, you can’t directly use a select case statement in an Access Query, but you can create a user defined function that uses the select case statement, Here is what you do:

1. create/copy your select case function called VMSCriteria() (for example) to a standard module (it needs to be a public function to be used with a query).
2. You can then reference the function in the criteria row of the field in your query grid as follows: VMSCriteria()

I'm not using the select case in the sql statement . I have a form that has the select case and depending what option the user picks on the form the apropriat query will exicute.

[puppydogbuddy]

ok, I misunderstood you. In taking another look, your error is a misspelled reference. Repor should be Report as indicated below:

& " WHERE ((([VMSU-CLT].Date)>=[forms]![Report query]![DateFrom] And ([VMSU-CLT].Date)<=[forms]![Repor<<<<<<<<<<< query]![DateTo])))"

[Scott Price]

If this is broken into two lines, as your error indicates, you need to add the underscore _ character to tell VBA that the statement is continued on the next line.

I notice that you have it in several places, but on that particular line not.

Regards,
Scott

[Trevor2007]

If this is broken into two lines, as your error indicates, you need to add the underscore _ character to tell VBA that the statement is continued on the next line.

I notice that you have it in several places, but on that particular line not.

Regards,
Scott

That might help "_" :-p, somtimes another set of eyes helps with lines and lines of code,
I try it in the morning and let you know
I think there is more wrong then just that, becaue the query produces a report, so when I set my control source on my report to the form [Report query] I get an error stating that the form does not exist, when it does and I have even copied and pasted the form name into the control source of the report and got the same error.

[Scott Price]

The control source of your report should not be the name of the form, it should be the name of the query.

Regards,
Scott

[Trevor2007]

The control source of your report should not be the name of the form, it should be the name of the query.

Regards,
Scott

your right, but in this case I am trying to hardcode my query into a form so I won't have an actual query , so thats why I think in this case it would be the name of the form

[Scott Price]

Well, if that was working for you, you wouldn't be here, eh? :-)

A form cannot act as a record source for anything! The form is only a container through which you interact with the data stored in underlying tables and passed (usually) through queries (either stored or hard-coded as SQL statements called through code).

Is there a significant reason why you cannot have a regular copy of this query to base the report on?

Regards,
Scott