By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,747 Members | 1,759 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,747 IT Pros & Developers. It's quick & easy.

Select query, trying to hard code

P: 68
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])))"
Feb 18 '08 #1
Share this Question
Share on Google+
8 Replies


puppydogbuddy
Expert 100+
P: 1,923
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()
Feb 18 '08 #2

P: 68
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.
Mar 14 '08 #3

puppydogbuddy
Expert 100+
P: 1,923
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])))"
Mar 15 '08 #4

Scott Price
Expert 100+
P: 1,384
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
Mar 15 '08 #5

P: 68
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.
Mar 15 '08 #6

Scott Price
Expert 100+
P: 1,384
The control source of your report should not be the name of the form, it should be the name of the query.

Regards,
Scott
Mar 15 '08 #7

P: 68
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
Mar 16 '08 #8

Scott Price
Expert 100+
P: 1,384
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
Mar 16 '08 #9

Post your reply

Sign in to post your reply or Sign up for a free account.