Solved

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

Posted on 2008-06-16
12
857 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
ID: 21795219


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
ID: 21795344
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
ID: 21795403


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
ID: 21795421
open your query in design view (SQL view) delete (remove) the parentheses and save.
0
 

Author Comment

by:R B
ID: 21795427
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
ID: 21795462
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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 119

Expert Comment

by:Rey Obrero
ID: 21795567


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
ID: 21795636
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
ID: 21795673
you should see three Select statement printed.

post them  in order here
0
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
ID: 21795933
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
ID: 21796136
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
ID: 31467680
Thanks... Your latest coding with the extra " " worked.  sincerely, Raj.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

932 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

13 Experts available now in Live!

Get 1:1 Help Now