Run-time error '3075': Syntax error in query expression....

I'm trying to change the "Between" Date-Parameters in a Query.   I'm getting an error that reads:
Run-time error '3075': Syntax error in query expression
'((([_dbo_ncv_audit_val_allocs].PPEndDate) between #10/13/2007# and #05/24/2008#;'.

That's what I'm trying to change the dates to - with the current SQL reading :
SELECT [_dbo_ncv_audit_val_allocs].* INTO [Labor Distribution Output - Value Allocation] IN 'L:\$CA_Prod\Fy2008\AUD\RXB4_AUD_FY2008_MAYAUTOTEST9.mdb'
FROM _dbo_ncv_audit_val_allocs
WHERE ((([_dbo_ncv_audit_val_allocs].PPEndDate) Between #10/13/2007# And #6/7/2008#));

The dates 10/13/2007 and 5/24/2008 are within the query from where I want the parameters to copy:
SELECT [_dbo_ncv_audit_dates].*
FROM _dbo_ncv_audit_dates
WHERE ((([_dbo_ncv_audit_dates].PPEndDate) Between #10/13/2007# And #5/24/2008#));

Here's the Coding in the Module - which pulls by RunMacro - with the Macro having RunCode for Val Allocs ()

Option Compare Database
Option Explicit

Public Function ValAllocs()
Dim strSQLq1
Dim strwhere1
Dim strsqlq2
strSQLq1 = CurrentDb.QueryDefs("_AUDAuditDatesQry").SQL
strwhere1 = Mid(strSQLq1, InStr(strSQLq1, "Between"))
strsqlq2 = CurrentDb.QueryDefs("_AUDValAllocsQry2").SQL
strsqlq2 = Left(strsqlq2, InStr(strsqlq2, "between") - 1) & strwhere1
CurrentDb.QueryDefs("_AUDValAllocsQry2").SQL = strsqlq2
End Function

Please assist...   I've run this several times - but, can't figure out how to fix it.  When I say 'debug' upon the error,  the last line above is lit up in yellow.  

Thanking you for your time & help, sincerely yours, Raj.
R BAsked:
Who is Participating?
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
try this


Public Function ValAllocs()
Dim strSQLq1
Dim strwhere1
Dim strsqlq2
strSQLq1 = CurrentDb.QueryDefs("_AUDAuditDatesQry").SQL
strwhere1 = Mid(strSQLq1, InStr(strSQLq1, "Where"))
strsqlq2 = CurrentDb.QueryDefs("_AUDValAllocsQry2").SQL
strsqlq2 = Left(strsqlq2, InStr(strsqlq2, "Where") - 1) & " " & strwhere1
CurrentDb.QueryDefs("_AUDValAllocsQry2").SQL = strsqlq2
End Function
0
 
Rey Obrero (Capricorn1)Commented:


Public Function ValAllocs()
Dim strSQLq1
Dim strwhere1
Dim strsqlq2
strSQLq1 = CurrentDb.QueryDefs("_AUDAuditDatesQry").SQL
strwhere1 = Mid(strSQLq1, InStr(strSQLq1, "Between"))
strsqlq2 = CurrentDb.QueryDefs("_AUDValAllocsQry2").SQL
strsqlq2 = Left(strsqlq2, InStr(strsqlq2, "between") - 1) & strwhere1

debug.print  strsqlq2      'add this line and view what was printed in the immediate window,  copy and post here

'comment this line for  the meantime
    'CurrentDb.QueryDefs("_AUDValAllocsQry2").SQL = strsqlq2


End Function
0
 
R BAuthor Commented:
The Immeidate Window does show the correct dates, but when I look at the Query itself - it has "not" changed.....   Also - I could not run this by hitting 'run' in the MVisualBasic screen.  I had come to the Macro where I have RunCode ValAllocs ().   When I hit run-macro, the query itself had not changed, but the Immediate Window in MVB-Screen had populated to show:
SELECT [_dbo_ncv_audit_val_allocs].* INTO [Labor Distribution Output - Value Allocation] IN 'L:\$CA_Prod\Fy2008\AUD\RXB4_AUD_FY2008_MAYAUTOTEST9.mdb'
FROM _dbo_ncv_audit_val_allocs
WHERE ((([_dbo_ncv_audit_val_allocs].PPEndDate) between #10-13-2007# and #05-24-2008#;

Please advise how to fix it so that the query itself will change.   I'll need to place this as a step within another macro that has 5 to 6 other steps... one of them being changing the query-output-destination-name (which is working well) and next will be changing the dates to the parameter dates within the _AUDAuditDatesQry .

Thanking you for your timely help, sincerely Raj.

0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
Rey Obrero (Capricorn1)Commented:


this is your syntax error is coming from  ( extra "(" parenthesis )

----------vv
WHERE ((([_dbo_ncv_audit_val_allocs].PPEndDate) between #10-13-2007# and #05-24-2008#;
0
 
Rey Obrero (Capricorn1)Commented:
open your query in design view (SQL view) delete (remove) the parentheses and save.
0
 
R BAuthor Commented:
Here's what I'm trying to do:  In the coding below - I want to replace the date parameters to come from the parameters within _AUDAuditDatesQry - which is a query that resides within the same database.  

Earlier,  my manager wanted the dates coming from another table dbo_ncx_run_dates, but now - I am told that the dates have to copy from the _AUDAuditDatesQuery parameters.

Please advise how I can do this.  I could not fit the other coding into this coding - although I tried several times.  

Thanking you for your timely help, sincerely yours, Raj.

Option Compare Database
Option Explicit
Const cQueryName As String = "_AUDValAllocsQry2"
Const cFilePrefix = "*AUD_FY2008"
Const cDirName = "L:\$CA_Prod\Fy2008\AUD\"
 
Public Function LatestFileName(ByVal strDirName As String) As String
Dim strFName As String
Dim strLatestFName As String
Dim dtLastDate As Date
Dim dtCurrentDate As Date
 
    On Error Resume Next
    strLatestFName = Dir(strDirName & cFilePrefix & "*.mdb")
   
    If strLatestFName = "" Then Exit Function      'no existing files
    dtLastDate = FileDateTime(strDirName & strLatestFName)
   
    'loop through other files and check the dates
    Do
        strFName = Dir
        If strFName <> "" Then
            dtCurrentDate = FileDateTime(strDirName & strFName)
            If dtCurrentDate > dtLastDate Then
                dtLastDate = dtCurrentDate
                strLatestFName = strFName
            End If
        End If
           
    Loop While strFName <> ""
       
   
    LatestFileName = strDirName & strLatestFName
   
End Function
 
 
 
Public Function UpdateQueryDef()
Dim qdef As QueryDef
Dim strSQL As String
Dim strFileName As String
Dim rst As Recordset
    'On Error GoTo err_label
   
   
    'retrive the file name
    strFileName = LatestFileName(cDirName)
 
   
    'get the start and end dates
    'strSQL = "select TOP 1 StartDate, EndDate FROM  dbo_ncx_run_dates ORDER BY StartDate DESC"
    'strSQL = "select TOP 1 #10/13/2007# as StartDate, max(PPEndDate) as EndDate FROM dbo_ncx_run_dates where (PPEndDate is less than SysDate)"
    'strSQL = "select TOP 1 #10/13/2007# as StartDate, PPEndDate as EndDate FROM  dbo_ncx_run_dates ORDER BY PPEndDate DESC"
    strSQL = "select TOP 1 #10/13/2007# as StartDate, PPEndDate as EndDate FROM  dbo_ncx_run_dates WHERE PPEndDate < Date() ORDER BY PPEndDate DESC"

    Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
    If rst.EOF Then GoTo err_label  'no data in the linked table
   
    'update the query definition
    strSQL = "SELECT [_dbo_ncv_audit_val_allocs].* INTO [Labor Distribution Output - Value Allocation] IN '" & strFileName & "' " & _
            "FROM _dbo_ncv_audit_val_allocs WHERE ((([_dbo_ncv_audit_val_allocs].PPEndDate) Between #" & _
            Format(rst!StartDate, "Short Date") & "# And #" & Format(rst!EndDate, "Short date") & "#));"
           
    Set qdef = CurrentDb.QueryDefs(cQueryName)
    qdef.SQL = strSQL
   
resume_label:
    On Error Resume Next
    rst.Close
0
 
R BAuthor Commented:
I can't delete that one extra parenthesis.  It next prompts me with the same error - and tells me I have one too many at the end of that line.....   Here's that SQL line again below.  Please note that the end date is still showing 6/7/2008 - when it should have changed to 5/24/2008.   Thanks... sincerely yours, Raj.

SELECT [_dbo_ncv_audit_val_allocs].* INTO [Labor Distribution Output - Value Allocation] IN 'L:\$CA_Prod\Fy2008\AUD\RXB4_AUD_FY2008_MAYAUTOTEST9.mdb'
FROM _dbo_ncv_audit_val_allocs
WHERE ((([_dbo_ncv_audit_val_allocs].PPEndDate) Between #10/13/2007# And #6/7/2008#));
0
 
Rey Obrero (Capricorn1)Commented:


Public Function ValAllocs()
Dim strSQLq1
Dim strwhere1
Dim strsqlq2
strSQLq1 = CurrentDb.QueryDefs("_AUDAuditDatesQry").SQL
strwhere1 = Mid(strSQLq1, InStr(strSQLq1, "Between"))

debug.print strSQLq1  

strsqlq2 = CurrentDb.QueryDefs("_AUDValAllocsQry2").SQL

debug.print  strsqlq2  

strsqlq2 = Left(strsqlq2, InStr(strsqlq2, "between") - 1) & strwhere1

debug.print  strsqlq2      'add this line and view what was printed in the immediate window,  copy and post here

'enable this
  CurrentDb.QueryDefs("_AUDValAllocsQry2").SQL = strsqlq2


End Function


now you can check
0
 
R BAuthor Commented:
As soon as I enabled that line CurrentDb.QueryDefs("_AUDValAllocsQry2").SQL = strsqlq2 :  I ended up getting the same error message from the Macro - although the immediate box shows:

SELECT [_dbo_ncv_audit_val_allocs].* INTO [Labor Distribution Output - Value Allocation] IN 'L:\$CA_Prod\Fy2008\AUD\RXB4_AUD_FY2008_MAYAUTOTEST9.mdb'
FROM _dbo_ncv_audit_val_allocs
WHERE ((([_dbo_ncv_audit_val_allocs].PPEndDate) Between #10/13/2007# And #6/7/2008#));

SELECT [_dbo_ncv_audit_val_allocs].* INTO [Labor Distribution Output - Value Allocation] IN 'L:\$CA_Prod\Fy2008\AUD\RXB4_AUD_FY2008_MAYAUTOTEST9.mdb'
FROM _dbo_ncv_audit_val_allocs
WHERE ((([_dbo_ncv_audit_val_allocs].PPEndDate) between #10-13-2007# and #05-24-2008#;

** Can you see if you can just fix the other coding - so that it pulls the dates from _AUDAuditDatesQry instead of dbo_ncx_run_dates :  for both the Start and the End Dates in the Parameters for PPEndDate for Between #10/13/2007# And #05/24/2008# ??

That's my ultimate goal anyway - and it may be better to just have it all done (the naming change & the paramter dates copied) in one Module.

Please assist,  thanking you for your time,  sincerely, Raj.
0
 
Rey Obrero (Capricorn1)Commented:
you should see three Select statement printed.

post them  in order here
0
 
R BAuthor Commented:
Your Coding worked....

Can you please help me with putting the date-specs into the other one? - where it's trying to pick the parameter dates from dbo.ncx_run_dates.   I want it to pick the dates from _AUDAuditDatesQry instead.

Thanks....  sincerely,  Raj.
0
 
R BAuthor Commented:
Thanks... Your latest coding with the extra " " worked.  sincerely, Raj.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.