Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 882
  • Last Modified:

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.
0
R B
Asked:
R B
  • 6
  • 6
1 Solution
 
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
 
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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
 
Rey Obrero (Capricorn1)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
 
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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now