Solved

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

Posted on 2008-06-16
12
856 Views
Last Modified: 2013-11-27
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
Comment
Question by:R B
  • 6
  • 6
12 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility


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
 

Author Comment

by:R B
Comment Utility
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
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility


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
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
open your query in design view (SQL view) delete (remove) the parentheses and save.
0
 

Author Comment

by:R B
Comment Utility
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
 

Author Comment

by:R B
Comment Utility
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility


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
 

Author Comment

by:R B
Comment Utility
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
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
you should see three Select statement printed.

post them  in order here
0
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
Comment Utility
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
 

Author Comment

by:R B
Comment Utility
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
 

Author Closing Comment

by:R B
Comment Utility
Thanks... Your latest coding with the extra " " worked.  sincerely, Raj.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now