qdf.SQL = strSQL doesn't change my query! 1

[tamus121]

I want to change the SQL of my query depending on the users choice but this isn't happening. From what I read qdf.SQL = strSQL should be doing this. Any help to get me over this would be welcome.
tamus121

Dim stDocName As String
    stDocName = "PH4Part1"

    'Check to see that Ending Date is later than Beginning Date.
    If LastDateTxt < FirstDateTxt Then
        MsgBox "The Last Date must be later than the First Date."
        DoCmd.CancelEvent
        FirstDateTxt.SetFocus
        Exit Sub
    End If


Dim strSQL As String
Dim db As DAO.Database
Dim qdf As DAO.QueryDef

Set db = CurrentDb
Set qdf = db.QueryDefs("NotPH4Part1Query")

strSQL = "SELECT CustomerRecordsQuery.Name, CustomerRecordsQuery.RegNo, CustomerRecordsQuery.AreaCode, CustomerRecordsQuery.Address1, CustomerRecordsQuery.Address2, CustomerRecordsQuery.Address3, CustomerRecordsQuery.Town, CustomerRecordsQuery.County, PH4Part1.Crecords, PH4Part1.Dpassports, PH4Part1.Etraceback, PH4Part1.Fisolation, PH4Part1.Girrigating, PH4Part1.Action, PH4Part1.Insp, PH4Part1.DateInsp, Right([RegNo],4) AS Exper1 FROM CustomerRecordsQuery LEFT JOIN PH4Part1 ON CustomerRecordsQuery.[RegNo] = PH4Part1.[UKNI] WHERE "

If Frame39 = 1 Then
    strSQL = strSQL & "(((CustomerRecordsQuery.AreaCode) Like IIf([Forms]![PH4Part1Report]![AreaCmb]=""ALL"",""*"",[Forms]![PH4Part1Report]![AreaCmb])) AND ((PH4Part1.DateInsp)>=[Forms]![PH4Part1Report]![FirstDateTxt] AND (PH4Part1.DateInsp)<=[Forms]![PH4Part1Report]![LastDateTxt]);"
ElseIf Frame39 = 2 Then
    strSQL = strSQL & "((PH4Part1.DateInsp)<[Forms]![PH4Part1Report]![FirstDateTxt] And (PH4Part1.DateInsp)>[Forms]![PH4Part1Report]![LastDateTxt] Or (PH4Part1.DateInsp) Is Null));"
End If

    On Error Resume Next

    qdf.SQL = strSQL
    DoCmd.OpenReport stDocName, acPreview, , , acDialog
    
    If Err = 2501 Then Err.Clear

Set db = Nothing
Set qdf = Nothing

 

[TheAceMan1]

How are ya tamus121 . . .

On face value of your post origination I see no problems code wise. But I do see one Access wise.

[blue]Tables, Queries, Forms, Reports should not have the same names![/blue]

You have [blue]PH4Part1[/blue] as a table and a report. Change the report name and see if that fixes it.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[MajP]

I see a problem.

debug.print frame39.value
debug.print strSql
qdf.SQL = strSQL

How do you know there is not a logic problem or a code problem? The query sql could not be changing for a myriad of reasons

 

[TheAceMan1]

Howdy MajP . . .

[blue]How do you know there is not a logic problem or a code problem?[/blue]

I don't! ... however if I stop to consider the possibilities all I can do is ask questions. I'm just taking a direct stab at it to get the thread going, which I know will be corrected along the way if wrong.

I see an [blue]option group[/blue] which I believe defaults to 1. Lets just see what happens ...

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[MajP]

Sorry Ace Man, the rhetorical question was to the OP. I got no idea how the option group is set up so there could be a problem there, or who knows how the sql string evaluates. It may be impossible to return a 1 or 2 from the option group. My point to the OP was they should at least try to validate their code. Bottom line you should always validate a derived sql string before asking why it does not work.

 

[TheAceMan1]

tamus121 . . .

So ... ia a selection made in the [blue]option group[/blue] before the code is run?

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[tamus121]

Ok thanks for your posts

I changed the report name to PH4Part1Rpt - yes I misseed that having the table and report with the same name, but it doesn't make any difference to trying to update the SQL of the query.

The user selects one option button (from two) inside the frame. I used message box to see if it was working but had removed all these before posting the code but here is the out put and the result from strSQL

1

SELECT CustomerRecordsQuery.Name, CustomerRecordsQuery.RegNo, CustomerRecordsQuery.AreaCode, CustomerRecordsQuery.Address1, CustomerRecordsQuery.Address2, CustomerRecordsQuery.Address3, CustomerRecordsQuery.Town, CustomerRecordsQuery.County, PH4Part1.Crecords, PH4Part1.Dpassports, PH4Part1.Etraceback, PH4Part1.Fisolation, PH4Part1.Girrigating, PH4Part1.Action, PH4Part1.Insp, PH4Part1.DateInsp, Right([RegNo],4) AS Exper1 FROM CustomerRecordsQuery LEFT JOIN PH4Part1 ON CustomerRecordsQuery.[RegNo] = PH4Part1.[UKNI] WHERE (((CustomerRecordsQuery.AreaCode) Like IIf([Forms]![PH4Part1Report]![AreaCmb]="ALL","*",[Forms]![PH4Part1Report]![AreaCmb])) AND ((PH4Part1.DateInsp)>=[Forms]![PH4Part1Report]![FirstDateTxt] AND (PH4Part1.DateInsp)<=[Forms]![PH4Part1Report]![LastDateTxt]);

2

SELECT CustomerRecordsQuery.Name, CustomerRecordsQuery.RegNo, CustomerRecordsQuery.AreaCode, CustomerRecordsQuery.Address1, CustomerRecordsQuery.Address2, CustomerRecordsQuery.Address3, CustomerRecordsQuery.Town, CustomerRecordsQuery.County, PH4Part1.Crecords, PH4Part1.Dpassports, PH4Part1.Etraceback, PH4Part1.Fisolation, PH4Part1.Girrigating, PH4Part1.Action, PH4Part1.Insp, PH4Part1.DateInsp, Right([RegNo],4) AS Exper1 FROM CustomerRecordsQuery LEFT JOIN PH4Part1 ON CustomerRecordsQuery.[RegNo] = PH4Part1.[UKNI] WHERE ((PH4Part1.DateInsp)<[Forms]![PH4Part1Report]![FirstDateTxt] And (PH4Part1.DateInsp)>[Forms]![PH4Part1Report]![LastDateTxt] Or (PH4Part1.DateInsp) Is Null));

so I think everything is alright to here, unless there is something wrong with my built queries but I can't see it.
tamus

 

[TheAceMan1]

tamus121 . . .

The [blue]where clause[/blue] of your 1st SQL reveals 6 open parentheses and only 5 closing.

[blue]WHERE [red][b]((([/b][/red]CustomerRecordsQuery.AreaCode[red][b])[/b][/red] Like IIf([Forms]![PH4Part1Report]![AreaCmb]="ALL","*",[Forms]![PH4Part1Report]![AreaCmb][red][b]))[/b][/red] AND [red][b](([/b][/red]PH4Part1.DateInsp[red][b])[/b][/red]>=[Forms]![PH4Part1Report]![FirstDateTxt] AND [red][b]([/b][/red]PH4Part1.DateInsp)<=[Forms]![PH4Part1Report]![LastDateTxt][red][b])[/b][/red];[/blue]

Although one closing is missing I'm not thinking this is the problem as your 2nd SQL appears ok and I'd expect an error from the 1st SQL when you try to run the query. As a test to make sure its working, try something simpler like:

[blue]strSQL = "SELECT CustomerRecordsQuery.Name FROM CustomerRecordsQuery;" [green][b]only![/b][/green][/blue]

Then open the query from the query pane and check SQL view. Let us know if it works! ...

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[tamus121]

but you are so right Ace Man

the parentheses for the WHERE clause were wrong for 1st and 2nd SQL.

the simple one worked and then I fixed the parentheses for the 1st and both it and the simple one were working. So had another look at the parentheses for the 2nd and came up with this

strSQL = strSQL & "(PH4Part1.DateInsp)<[Forms]![PH4Part1Report]![FirstDateTxt] And (PH4Part1.DateInsp)>[Forms]![PH4Part1Report]![LastDateTxt] Or (PH4Part1.DateInsp) Is Null;"

thanks all for your input
tamus

 

[TheAceMan1]

tamus121 . . .

Great ... I didn't check position of parentheses, just the count of open & close.

Also Note: You've stumbled onto something good done by MS. It appears [purple]a QueryDef is not allowed to overwrite the SQL of a query with SQL thats syntactically incorrect![/purple]

[green]Good Job ...[/green]



See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]