Disappearing Operator?!?

[boostbyslinky]

So, this query is dynamically built in VBA and added to a new query..

SELECT DISTINCT dbo_ReportData.DOCKET, dbo_ReportData.site_search, Priority_Grouping.Category, Priority_Grouping.Priority, Prod_ShortCode.ShortCode, Prod_ShortCode.HighLevelGroup, dbo_ReportData.Description, Count(dbo_ReportData.[Job No]) AS [CountOfJob No], Sum(dbo_ReportData.[Response Time]) AS [SumOfResponse Time], Sum(dbo_ReportData.[Fix Time]) AS [SumOfFix Time], Min(dbo_ReportData.[Received Date]) AS [MinOfReceived Date], Max(dbo_ReportData.[Completion Date]) AS [MaxOfCompletion Date], Last(dbo_ReportData.[Fault Desc 1]) AS [LastOfFault Desc 1], Last(dbo_ReportData.[Fault Desc 2]) AS [LastOfFault Desc 2], Last(dbo_ReportData.[Fault Desc 3]) AS [LastOfFault Desc 3], Last(dbo_ReportData.[Work Done 1]) AS [LastOfWork Done 1], Last(dbo_ReportData.[Work Done 2]) AS [LastOfWork Done 2] FROM Prod_ShortCode INNER JOIN (Priority_Grouping INNER JOIN dbo_ReportData ON Priority_Grouping.Priority = dbo_ReportData.Priority) ON Prod_ShortCode.Description = dbo_ReportData.Description WHERE (((dbo_ReportData.[Log Num])
 In (1,2,3,5,6,12,24,36,99)) AND ((dbo_ReportData.[Completion Date]) BETWEEN 01/01/2011 AND 01/03/2011) AND ((dbo_ReportData.site_search)[B]=[/B]"TTTT001")) GROUP BY dbo_ReportData.DOCKET, dbo_ReportData.site_search, Priority_Grouping.Category, Priority_Grouping.Priority, Prod_ShortCode.ShortCode, Prod_ShortCode.HighLevelGroup, dbo_ReportData.Description ORDER BY Count(dbo_ReportData.[Job No]) DESC;

However, when attempting to run the query the = goes walkabout.. Adding double ='s into the VB does nothing other than generate errors..

Am I missing something simple?

TIA..

 

[MarkK]

the = goes walkabout

That's your description of the problem?

 

[jdraw]

I agree with lagbolt.

What is the Microsoft version of the error?
Symptoms?

Do you use # to delimit dates?

 

[boostbyslinky]

Yes, that's the description of my problem..

stepping through the VBA and using the "immediates" window to ascertain the value generated for strsql (code below) includes the "=" prior the variable passed as "Data_Select_1" ... Copying that out of the immediates window and pasting it into a SQL type query in Access leads to the aforementioned "=" going missing...

Private Sub Command39_Click()
Dim DB As DAO.Database
Dim QD As DAO.QueryDef
Dim strSql As String
Set DB = CurrentDb
Dim QDF As DAO.QueryDef

For Each QDF In CurrentDb.QueryDefs
    If QDF.Name = "Temp_Query" Then
        CurrentDb.QueryDefs.Delete "Temp_Query"
    Exit For
  End If
Next

strsql1 = ""
strsql2 = ""
strsql3 = ""
strsql4 = ""
strsql5 = ""
strsql6 = ""
strsql7 = ""
strsql7 = ""
strsql8 = ""
strsql9 = ""
strsql10 = ""
strsql11 = ""

If Field_Select_1 = "Site_Reference" Then
    strsql1 = "SELECT DISTINCT dbo_ReportData.DOCKET, dbo_ReportData.site_search, Priority_Grouping.Category, Priority_Grouping.Priority, Prod_ShortCode.ShortCode, Prod_ShortCode.HighLevelGroup, dbo_ReportData.Description, Count(dbo_ReportData.[Job No]) AS [CountOfJob No], Sum(dbo_ReportData.[Response Time]) AS [SumOfResponse Time], Sum(dbo_ReportData.[Fix Time]) AS [SumOfFix Time], Min(dbo_ReportData.[Received Date]) AS [MinOfReceived Date], Max(dbo_ReportData.[Completion Date]) AS [MaxOfCompletion Date], Last(dbo_ReportData.[Fault Desc 1]) AS [LastOfFault Desc 1], Last(dbo_ReportData.[Fault Desc 2]) AS [LastOfFault Desc 2], Last(dbo_ReportData.[Fault Desc 3]) AS [LastOfFault Desc 3], Last(dbo_ReportData.[Work Done 1]) AS [LastOfWork Done 1], Last(dbo_ReportData.[Work Done 2]) AS [LastOfWork Done 2] "
    strsql2 = "FROM Prod_ShortCode INNER JOIN (Priority_Grouping INNER JOIN dbo_ReportData ON Priority_Grouping.Priority = dbo_ReportData.Priority) ON Prod_ShortCode.Description = dbo_ReportData.Description "
    strsql3 = "WHERE (((dbo_ReportData.[Log Num]) In (1,2,3,5,6,12,24,36,99)) AND ((dbo_ReportData.[Completion Date]) BETWEEN "
    strsql4 = Start_Date
    strsql5 = " AND "
    strsql6 = End_Date
    strsql7 = ") AND ((dbo_ReportData.site_search)="""
    strsql8 = Data_Select_1
    strsql10 = """)) GROUP BY dbo_ReportData.DOCKET, dbo_ReportData.site_search, Priority_Grouping.Category, Priority_Grouping.Priority, Prod_ShortCode.ShortCode, Prod_ShortCode.HighLevelGroup, dbo_ReportData.Description "
    strsql11 = "ORDER BY Count(dbo_ReportData.[Job No]) DESC;"
    strSql = strsql1 & strsql2 & strsql3 & strsql4 & strsql5 & strsql6 & strsql7 & strsql8 & strsql10 & strsql11

Else
End If

'Create Query "Temp_Query"
Set QD = DB.CreateQueryDef("Temp_Query", strSql)
'Run Query "Temp_Query"
DoCmd.OpenQuery "Temp_Query"

End Sub

 

[boblarson]

A couple of suggestions

1. it seems a bit pointless to use that many variables when you can simply use

strSQL = strSQL & "whatever And "
strSQL = strSQL & "the next thing"

2. I don't see any missing = sign in your first post. It is there fine but you do need to encapsulate the dates with octothorpes (#) as jdraw had mentioned:

strsql3 = "WHERE (((dbo_ReportData.[Log Num]) In (1,2,3,5,6,12,24,36,99)) AND ((dbo_ReportData.[Completion Date]) BETWEEN #"
strsql4 = Start_Date
strsql5 = "# AND #"
strsql6 = End_Date
strsql7 = "#) AND ((dbo_ReportData.site_search)="""

 

[boostbyslinky]

Cheers Bob, yes, I agree that there are too many variables, but I'm going through a debug process at the moment to try to work out where the query builder (which I'm attempting to build) is falling down..

Looking at the generated "SQL", the issue seems to lie in my WHERE clause..

Here's the clause..

WHERE (((dbo_ReportData.[Log Num]) In (1,2,3,5,6,12,24,36,99)) AND ((dbo_ReportData.[Completion Date]) BETWEEN #01/02/2011# AND #01/03/2011#) AND ((dbo_ReportData.site_search)="TTTT001"))

You'll see that (dbo_ReportData.site_search)="TTTT001") includes the "=" operator, yet viewing the query in design view does not show the =... Adding the = within query designer makes the query work, but makes NO changes to the underlying "SQL" query...

That's the confusing bit!

 

[boostbyslinky]

I moved this on a little, in that I've discovered that the missing operator isn't the issue!

The discovery is that the date parameter is being (somehow) converted into US format (I think!) but I can find nowhere that would lead to this happening.. I have tried specifying a format for the text box to force it to DD/MM/YYYY but this seems to make little difference..

So, a little confused on that one..

 

[boblarson]

Read this and I think it will become clear.

 

[boostbyslinky]

Perfect!! Many thanks Bob, we're up and running..

Cheers....