query

[Trevor]

I am using an access query builder and in sql view I got the sql statment for my query, witch I am try to place the sql query into my code so I don't need to call to the access query to run the query. anyway I copied and pased the query into my code and it gives me an error right after select error "case expected".... the query below builds a report using fieldes and pulls the date from form report query


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]![Report query]![DateTo]));



note; I would also like to disaay it in a fashion in my code simler to this so I don't have to scroll over to see 2 lines of a long query I tried using & _ but I still gt that error case expected so I'm hopping once that error is sovled I can use the & _

[asingh]

How are you using..the above statement..in a VBA environment..????

[Trevor]

I'm using it to display the select fields in a report by the date from and date to , where the dates are taken from a form , and Im trying to hard code the query into the a form so that I don't have to use the access query builder. the query works fine through the access query screen but doesnt seem to want to work when I copy and past it into a form.

[asingh]

Paste the whole code here....vba..

[Trevor]

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]![Report query]![DateTo]));

** everything above as it appears in SQL view***

[Trevor]

its going into a select case so I don't think the code matters(if you are assking for aditional code other then the query statement) , becase its a simple radial button fram , case 0, case 1 case 2 .... etc.

[asingh]

Okay..if you are passing the above as a string..it should be...

"SELECT [VMSU-CLT].Date, [VMSU-CLT].IDNumber, [VMSU-CLT].Time," _
& "[VMSU-CLT].CorrespondenceType,....."

[Trevor]

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

"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])))"

[Tommy]

You need to assign the SQL to a string and send the string.

strSQL = "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]![Report query]![DateTo]));"



HTH

[Trevor]

Thanks, I could have swarn that I tried that before posting, but I must had missed somthing but I am now recieveing an error: record source Forms![Report Query]!repotOpt does not exsist.
What I have is a report date selector, to select the dates to report from and to and I have an option box to select the report, and I am trying to run the query and open the report from this , my report has a record source of Forms![Report Query]!ReportOpt.
and here is my code to run the query and open the report.

Private Sub Print_Preview_Click()
On Error GoTo Err_cmdReport_Click
Dim StrSQL As String
Dim stDocName As String
Select Case ReportOpt
Case 1
StrSQL = "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]![Report query]![DateTo]));"
stDocName = "Call Report"
Case 2
stDocName = "Mail Report"
End Select
'Check values are entered into Date From and Date To text boxes
'if so run report or cancel request
If Len(Me.ReportOpt & vbNullString) = 0 Then MsgBox "This requires a report selection from the option list", vbInformation, _
"Selection Required..."
If Len(Me.DateFrom & vbNullString) = 0 Or Len(Me.DateTo & vbNullString) = 0 Then
MsgBox "Please ensure that a report date range is entered into the form", _
vbInformation, "Required Data..."
Exit Sub
Else
DoCmd.OpenReport stDocName, acPreview
End If
Exit_Print_Preview_Click:
Exit Sub
Err_cmdReport_Click:
MsgBox Err.Description
Resume Exit_Print_Preview_Click

i'm thinking that becaue my case statment controles the query and what report to open that it should work with my controle source for my report being Forms![Report Query] but it doen't seem to be working that way