Recordsets and VBSQL + Date as String formatting

[asdaf3814]

Hello all,

Coming back for help again. I'm stumped on something that I think is simple, and have grown tired of trial and error and googling for help.


I have one main issue, and one question.

The main issue:

I am using Access 2003. Running an ODBC connection to query a linked table. This table contains dates, sales figures, information, etc. Essentially a master table. We are running one main query to pull all the data that we need; and then several local queries once this data is retrieved. I can not use local tables when querying against the ODBC linked table.

Locally, we have a table of information and account numbers. This changes all the time and can be one account number to several hundred.
The account numbers in my local table and the ODBC table are text fields. 10 characters. They are the same format.

My query is pulling all the information that I need, and I would like one of the criteria's to be my local accounts table. So I'm using a recordset and access vba.

When I change the criteria of the accounts numbers from my vba string to an actual account number. The query works fine. It will only fail when I use the string. I believe the formatting is correct, but this could be the issue.

***Please note, I'm only including the criteria portion of my query, as the main one is quite long and I know the query works when replacing the string with an actual account number.

    Dim myRecordset As ADODB.Recordset
    Dim strSQL As String
    Dim strOutput As String
    Dim strFinal As String

    strSQL = "SELECT ACCOUNTS FROM AccountsForForms"
    
    Set myConnection = CurrentProject.Connection
    Set myRecordset = CurrentDb.OpenRecordset(strSQL, DB_OPEN_DYNASET, adOpenForwardOnly)
 
    Do Until myRecordset.EOF
       strOutput = strOutput + myRecordset.Fields("ACCOUNTS") & ", " & vbCrLf
       myRecordset.MoveNext
    Loop

    strFinal = strOutput

    myRecordset.Close
    myConnection.Close
    Set myConnection = Nothing
    Set myRecordset = Nothing


    DoCmd.SetWarnings False
    
    'Clears all tables first
    DoCmd.RunSQL "DELETE * FROM MyQueryData"
          
    strSQLQ = strSQLQ & "FROM ODBCTable WHERE ODBCTable.InvoiceDate BETWEEN #6/30/12# AND #10/27/12# AND  ODBCTable.AccountNumber =" & "'" & strFinal & "'" & "  AND ODBCTable.RunDate BETWEEN #8/2/12# AND #10/27/12# AND   ODBCTable.CustomerType='00';"


    Debug.Print strSQLQ
    DoCmd.RunSQL strSQLQ
    
    MsgBox "Tables Updated", vbExclamation
    Beep
    
End Sub

EDIT:: I think as I'm typing this, I realize my mistake.
Is it not recognizing the characters in the string, because of this;
strOutput = strOutput + myRecordset.Fields("ACCOUNTS") & ", " & vbCrLf

I think this may be the issue, that this is breaking it up into individual lines. The immediate window is displaying this as well. I think my thinking was that if I was able to break up the account numbers it would read through it one by one. But I believe I just realized that was more than likely a stupid mistake.


Second question:

Date formats and strings.

Is there a correct way to use a string as a date criteria in a query?

Example: My Form has a text box with the control source being Date()-30

Is is it possible to set this date to a string, then run a query with the string as a criteria? Or is this just a terrible train of thought? I would like to have it set up in this fashion, or something similar, to avoid having to manually update the date criteria each time. I can set it so specific periods and just have it pass through to a string.

 
    Dim StrDte1 As String    
    Dim StrDte2 As String
    Dim StrDteF1 As String
    Dim StrDteF2 As String
    Dim Dte1 As Date
    Dim Dte2 As Date
   
    StrDte1 = Me.Text3.Value
    StrDte2 = Me.Text5.Value
    
    Dte1 = CDate(Format(StrDte1, "mm/dd/yyyy"))
     Dte2 = CDate(Format(StrDte2, "mm/dd/yyyy"))
     
     StrDteF1 = Dte1
     StrDteF2 = Dte2

    strSQLQ = strSQLQ & "FROM ODBCTable WHERE ODBCTable.InvoiceDate BETWEEN '" & StrDteF1 & "' AND '" & StrDteF1 & "'  AND ODBCTable.AccountNumber = '11C111E1' AND ODBCTable.RunDate BETWEEN #8/2/12# AND #10/27/12# AND ODBCTable.CustomerType='00';"


    Debug.Print strSQLQ
    DoCmd.RunSQL strSQLQ
     
    MsgBox "Tables Updated", vbExclamation
    Beep
    
End Sub

Any help/criticism or words of advice would be greatly appreciated.

Thanks again for any help or recommendations.

[orange]

Is the linked table (ODBC) also an Access table (or is it SQLServer, Oracle.,...) ?

In Access dates are delimited with # eg #11/29/2012#

[asdaf3814]

Hi Orange,

Argh. Sorry for not mentioning this. It's Oracle. It's connecting to a view of the main table. The main table is split into separate indexes.

[orange]

Are you running a Pass through query or, as it appears, just a regular Access query? If Pass through you must use the SQL that Oracle recognizes.
You have a debug.print strSQLQ -- what gets produced? You have strSQL and strSQLQ????? is that right?
From a technique perspective I'd put a space " " before the FROM just to ensure proper syntax.
Also when you post code(question 2), you should include all the code.

[asdaf3814]

Orange,

It is a regular Access query.

As for the code on question 2, I know. I apologize. But I was just asking about formatting the dates into a string and using this a query, it's essentially the same code as first one. I was just looking at options of potentially adding this in as well.
I know that is frustrating from a tracing point of view.


The debug displays something similar to this:

"FROM ODBCTable WHERE ODBCTable.InvoiceDate BETWEEN #6/30/12# AND #10/27/12# AND  ODBCTable.AccountNumber ='11C111E1,
11hCk11E1,
11C1311E1,
1210F1I11,
11C111E12,
1D119117R,
C11d81EB1,
31C1114X1,' AND ODBCTable.RunDate BETWEEN #8/2/12# AND #10/27/12# AND   ODBCTable.CustomerType='00';"

So it basically returns my query, and displays the account numbers in order.

If I copy that result, and put it into a new query it will work as long as the account number are in the proper syntax. Example, in Access Non VBA this will work: ODBCTable.AccountNumber IN ("11hCk11E1","11C1311E1","1210F1I11T","11C111E12","1D119117R","C11d81EB1").

But this requires users to manually add these accounts in. Which is why I'm trying to go the VBA route with this. There is just something wrong with my process or syntax and its driving me crazy.

[orange]

The debug will have/should have a SELECT...
Please post all your code for the one with the issue.

If I copy that result, and put it into a new query it will work as long as the account number are in the proper syntax. Example, in Access Non VBA this will work: ODBCTable.AccountNumber IN ("11hCk11E1","11C1311E1","1210F1I11T","11C111E12","1D119117R","C11d81EB1").

I will help you build the query in vba.

[orange]

Here's a sample of building an sql statement in vba. I used a table of mine (Customers - using CustomerId) to simulate your AccountNumber.
The logic is to identify the pieces of the SQL query's WHERE clause (conditions you need).
Since your dates were fixed -- you knew the values, I put them into 2 strings.
InvoiceDateCondition and RunDateCondition. I'm guessing a bit at what you were doing, but I used the first recordset to get the AccountNumbers for the IN statement. Then built the IN statement and removed trailing pieces and added a closing bracket.
Then just pieced it together.
Base sql (SELECT field21, field2.. from ODBCTable WHERE " & DateCondition1 & " AND " & DateCondition2 & " AND " & InStatement.

Here's the vba code

Sub sqlq()

    Dim db As DAO.Database
    Dim myrecordset As DAO.Recordset
    Dim strSQL As String
    Dim strIN As String  'string to hold the IN statement

    Dim strOutput As String
    Dim strFinal As String
    
    Dim RunDateCondition As String
    RunDateCondition = " ODBCTable.RunDate BETWEEN #8/2/12# AND #10/27/12# "
    
    Dim InvoiceDateCondition As String
    InvoiceDateCondition = "ODBCTable.InvoiceDate BETWEEN #6/30/12# AND #10/27/12# "

    ' strSQL = "SELECT ACCOUNTS FROM AccountsForForms"
    strSQL = "SELECT  customerId FROM Customers "

    'Set myconnection = CurrentProject.Connection
    Set db = CurrentDb
    Set myrecordset = db.OpenRecordset(strSQL)
    strOutput = " ACCOUNTNumber IN ("""
    
    '  Loop to build the IN statement
    Do Until myrecordset.EOF
        strOutput = strOutput & "" & myrecordset.Fields("CustomerId") & """, """  '& vbCrLf
        myrecordset.MoveNext
    Loop
    '
    'The IN statement is Too Long, must remove quotes and comma and replace with  bracket
    strIN = Mid(strOutput, 1, Len(strOutput) - 3) & ")"
    
    ' Debug.Print strIN
    
    'build the final sql statement -- Base + conditions + the IN statement
    strFinal = " Select field1, field2 From ODBCTable Where " _
               & InvoiceDateCondition & " AND " _
               & RunDateCondition & " AND " _
               & strIN & ";"
    Debug.Print strFinal;
    myrecordset.Close
    '  myconnection.Close
    '  Set myconnection = Nothing
    '  Set myrecordset = Nothing


    ' DoCmd.SetWarnings False

    'Clears all tables first
    'DoCmd.RunSQL "DELETE * FROM MyQueryData"

    ' strSQL = strSQL & " FROM ODBCTable WHERE ODBCTable.InvoiceDate BETWEEN #6/30/12# AND #10/27/12# AND  ODBCTable.AccountNumber IN " & "'" & strFinal & "'" & "  AND ODBCTable.RunDate BETWEEN #8/2/12# AND #10/27/12# ;"


    ' Debug.Print strSQL
    ' DoCmd.RunSQL strSQLQ


    MsgBox "Tables Updated", vbExclamation
    Beep

End Sub

Here's a sample of the output:

 Select field1, field2 From ODBCTable 
Where ODBCTable.InvoiceDate BETWEEN #6/30/12# AND #10/27/12#  AND 
 ODBCTable.RunDate BETWEEN #8/2/12# AND #10/27/12#  AND
  ACCOUNTNumber IN
 ("ALFKI", "ANATR", "ANTON", "AROUT", "BERGS", "BLAUS", "BLONP",
 "BOLID", "BONAP", "BOTTM", "BSBEV", "CACTU", "CENTC", "CHOPS",
 "COMMI", "CONSH", "DRACD", "DUMON", "EASTC", "ERNSH", "FAMIA",
 "FISSA", "FOLIG", "FOLKO", "FRANK", "FRANR", "FRANS", "FURIB",
 "GALED", "GODOS", "GOURL", "GREAL", "GROSR", "HANAR", "HILAA",
 "HUNGC", "HUNGO", "ISLAT", "KOENE", "LACOR", "LAMAI", "LAUGB",
 "LAZYK", "LEHMS", "LETSS", "LILAS", "LINOD", "LONEP", "MAGAA", 
 "MAISD", "MEREP", "MORGK", "NORTS", "OCEAN", "OLDWO", "OTTIK",
 "PARIS", "PERIC", "PICCO", "PRINI", "QUEDE", "QUEEN", "QUICK", "RANCH",
 "RATTC", "REGGC", "RICAR", "RICSU", "ROMEY", "SANTG", "SAVEA",
 "SEVES", "SIMOB", "SPECD", "SPLIR", "SUPRD", "THEBI", "THECR",
 "TOMSP", "TORTU", "TRADH", "TRAIH", "VAFFE", "VICTE", "VINET",
 "WANDK", "WARTH", "WELLI", "WHITC", "WILMK", "WOLZA");

Good luck with your project.

[asdaf3814]

Here's a sample of building an sql statement in vba. I used a table of mine (Customers - using CustomerId) to simulate your AccountNumber.
The logic is to identify the pieces of the SQL query's WHERE clause (conditions you need).
Since your dates were fixed -- you knew the values, I put them into 2 strings.
InvoiceDateCondition and RunDateCondition. I'm guessing a bit at what you were doing, but I used the first recordset to get the AccountNumbers for the IN statement. Then built the IN statement and removed trailing pieces and added a closing bracket.
Then just pieced it together.
Base sql (SELECT field21, field2.. from ODBCTable WHERE " & DateCondition1 & " AND " & DateCondition2 & " AND " & InStatement.

Here's the vba code

Sub sqlq()

    Dim db As DAO.Database
    Dim myrecordset As DAO.Recordset
    Dim strSQL As String
    Dim strIN As String  'string to hold the IN statement

    Dim strOutput As String
    Dim strFinal As String
    
    Dim RunDateCondition As String
    RunDateCondition = " ODBCTable.RunDate BETWEEN #8/2/12# AND #10/27/12# "
    
    Dim InvoiceDateCondition As String
    InvoiceDateCondition = "ODBCTable.InvoiceDate BETWEEN #6/30/12# AND #10/27/12# "

    ' strSQL = "SELECT ACCOUNTS FROM AccountsForForms"
    strSQL = "SELECT  customerId FROM Customers "

   'Set myconnection = CurrentProject.Connection
    Set db = CurrentDb
    Set myrecordset = db.OpenRecordset(strSQL)
    strOutput = " ACCOUNTNumber IN ("""
    
    '  Loop to build the IN statement
    Do Until myrecordset.EOF
        strOutput = strOutput & "" & myrecordset.Fields("CustomerId") & """, """  '& vbCrLf
        myrecordset.MoveNext
    Loop
    '
    'The IN statement is Too Long, must remove quotes and comma and replace with  bracket
    strIN = Mid(strOutput, 1, Len(strOutput) - 3) & ")"
    
   ' Debug.Print strIN
    
   'build the final sql statement -- Base + conditions + the IN statement
    strFinal = " Select field1, field2 From ODBCTable Where " _
               & InvoiceDateCondition & " AND " _
               & RunDateCondition & " AND " _
               & strIN & ";"
    Debug.Print strFinal;
    myrecordset.Close
   '  myconnection.Close
    '  Set myconnection = Nothing
    '  Set myrecordset = Nothing


    ' DoCmd.SetWarnings False

    'Clears all tables first
    'DoCmd.RunSQL "DELETE * FROM MyQueryData"

 ' strSQL = strSQL & " FROM ODBCTable WHERE ODBCTable.InvoiceDate BETWEEN #6/30/12# AND #10/27/12# AND  ODBCTable.AccountNumber IN " & "'" & strFinal & "'" & "  AND ODBCTable.RunDate BETWEEN #8/2/12# AND #10/27/12# ;"


    ' Debug.Print strSQL
    ' DoCmd.RunSQL strSQLQ


    MsgBox "Tables Updated", vbExclamation
    Beep

End Sub

Here's a sample of the output:

 Select field1, field2 From ODBCTable 
Where ODBCTable.InvoiceDate BETWEEN #6/30/12# AND #10/27/12#  AND 
 ODBCTable.RunDate BETWEEN #8/2/12# AND #10/27/12#  AND
  ACCOUNTNumber IN
 ("ALFKI", "ANATR", "ANTON", "AROUT", "BERGS", "BLAUS", "BLONP",
 "BOLID", "BONAP", "BOTTM", "BSBEV", "CACTU", "CENTC", "CHOPS",
 "COMMI", "CONSH", "DRACD", "DUMON", "EASTC", "ERNSH", "FAMIA",
 "FISSA", "FOLIG", "FOLKO", "FRANK", "FRANR", "FRANS", "FURIB",
 "GALED", "GODOS", "GOURL", "GREAL", "GROSR", "HANAR", "HILAA",
 "HUNGC", "HUNGO", "ISLAT", "KOENE", "LACOR", "LAMAI", "LAUGB",
 "LAZYK", "LEHMS", "LETSS", "LILAS", "LINOD", "LONEP", "MAGAA", 
 "MAISD", "MEREP", "MORGK", "NORTS", "OCEAN", "OLDWO", "OTTIK",
 "PARIS", "PERIC", "PICCO", "PRINI", "QUEDE", "QUEEN", "QUICK", "RANCH",
 "RATTC", "REGGC", "RICAR", "RICSU", "ROMEY", "SANTG", "SAVEA",
 "SEVES", "SIMOB", "SPECD", "SPLIR", "SUPRD", "THEBI", "THECR",
 "TOMSP", "TORTU", "TRADH", "TRAIH", "VAFFE", "VICTE", "VINET",
 "WANDK", "WARTH", "WELLI", "WHITC", "WILMK", "WOLZA");

Good luck with your project.

Orange,

That worked. I adjusted it to work with my set up, and it worked perfectly. THANK YOU!!! I owe you alot, I've been bashing my head trying to figure a way to get this to work. This way is much more logical, and in fact, I went back and adjusted many other very large queries to this style. Much cleaner, simpler.Thank you again.


I do have one follow up question. Regarind the dates in my query. What is the correct way to format a string to represent a date field?

If I want to have hidden text fields on my form, that simply have =Date() and the second text box has =Date()-90
Is there a way for my criteria to be based between these two?

Example:

 Dim RunDateCondition As String
    Dim strDte1 As String
    Dim strDte2 As String
    Dim Dte1 As String
    Dim Dte2 As String
    
    'DateOld & DateNew are my textboxes on this form. They show Todays Date, and Todays Date - 90 Days. 
    Dte1 = Me.DateOld.Value
    Dte2 = Me.DateNew.Value
    
    
    
    strDte1 = Format(DateValue(Dte1), "mm/dd/yyyy")
    strDte2 = Format(DateValue(Dte2), "mm/dd/yyyy")
     
    'RunDateCondition = " O858IA_VWRDCAM16.INV_DT BETWEEN #6/30/12# AND #10/27/12# "
    RunDateCondition = " O858IA_VWRDCAM16.INV_DT BETWEEN " & strDte1 & " AND " & strDte2 & " "
    
    Dim InvoiceDateCondition As String
    Dim strDte3 As String
    Dim strDte4 As String
    Dim Dte3 As String
    Dim Dte4 As String
    
    'DateOld & DateNew are my textboxes on this form. They show Todays Date, and Todays Date - 90 Days. 
    Dte3 = Me.DateOld.Value
    Dte4 = Me.DateNew.Value
 
    strDte3 = Format(DateValue(Dte3), "mm/dd/yyyy")
    strDte4 = Format(DateValue(Dte4), "mm/dd/yyyy")
    
    'InvoiceDateCondition = " O858IA_VWRDCAM16.PRC_JOB_LST_RUN_DT BETWEEN #6/30/12# AND #10/27/12# "
    InvoiceDateCondition = " O858IA_VWRDCAM16.INV_DT BETWEEN " & strDte3 & " AND " & strDte4 & " "

The error I recieve is Oracle expected Date, got Numer. I believe this is due to this: " & strDte4 & " style of formating that string. Is this a correct way to format this string so it would be equal to a date value?

[orange]

Look up DateDiff function.

[ssanfu]

'RunDateCondition = " O858IA_VWRDCAM16.INV_DT BETWEEN #6/30/12# AND #10/27/12# "

Does the query execute if you hard code the dates (like above)?

If so, you need to add the delimiters for dates. You have two options:

1) add the delimiters

    RunDateCondition = " O858IA_VWRDCAM16.INV_DT BETWEEN #" & strDte1 & "# AND #" & strDte2 & "#"

2) change the format string:

    strDte1 = Format(Me.DateOld, "\#mm/dd/yyyy\#")
    strDte2 = Format(Me.DateNew, "\#mm/dd/yyyy\#")

Note that I changes the above a little. Also, the ".Value" is not needed because the value property is the default property. (doesn't hurt, but is more typing)