Query SQL to VBA code for use as a recordset

jdlewin1

Registered User.
Local time
Today, 00:20
Joined
Apr 4, 2017
Messages
92
Hi I have formulated a UNION query to give me a result set which is filtered using a the Asset field on a form called Reserve. I need to put the dates into "dd/mm/yyyy" format as some are Date & Time hence the format parts.

I am not great at converting query SQl to VBA code......can anyone let me have the correct coding to convert this to the correct code for my VBA.....

Code:
SELECT Reservations.[Job Number], Reservations.Asset, Left([First Name],1) & Left([Last Name],1) AS Initials, Format([Checked Out Date],"dd/mm/yyyy") AS FCheckOut, IIf([Checked In Date] Is Null,Format([Due Date],"dd/mm/yyyy"),Format([Checked In Date],"dd/mm/yyyy")) AS FInDate
FROM Reservations LEFT JOIN Contacts ON Reservations.[Checked Out To] = Contacts.ID
WHERE (((Reservations.Asset)=[Forms]![Reserve]![Asset]))
UNION ALL SELECT Transactions.[Job Number], Transactions.Asset, Left([First Name],1) & Left([Last Name],1) AS Initials, Format([Checked Out Date],"dd/mm/yyyy") AS FCheckOut, IIf([Checked In Date] Is Null,Format([Due Date],"dd/mm/yyyy"),Format([Checked In Date],"dd/mm/yyyy")) AS FInDate
FROM Transactions LEFT JOIN Contacts ON Transactions.[Checked Out To] = Contacts.ID
WHERE (((Transactions.Asset)=[Forms]![Reserve]![Asset]));

Hoping one of you coding guru's can help!!!!

Cheers
 
Don't convert it, just make a query.
The query can read the date off the form.
 
I need it in VBA code form as it will make part of a much larger code sequence.
 
For info, inspired by the tool by Allen Browne, I have extended the idea to convert in either direction. Also to create a query in the QDE based on the SQL created.

I will upload to the code repository in the near future
 
Thanks Hans.....I will give this ago and see if it works.

If anyone can post the vba code of my SQL above in the meantime that would be great!!!

:banghead:
 
Hi

If you are saying the union query is OK, here is the conversion you need:

Code:
DoCmd.SetWarnings False
DoCmd.RunSQL "SELECT Reservations.[Job Number], Reservations.Asset, Left([First Name],1) & Left([Last Name],1) AS Initials," & _
	" Format([Checked Out Date],"dd/mm/yyyy") AS FCheckOut, IIf([Checked In Date] Is Null, Format([Due Date],"dd/mm/yyyy")," & _
	" Format([Checked In Date],"dd/mm/yyyy")) AS FInDate" & _
	" FROM Reservations LEFT JOIN Contacts ON Reservations.[Checked Out To] = Contacts.ID" & _
	" WHERE (((Reservations.Asset)=[Forms]![Reserve]![Asset]))" & _
	" UNION ALL SELECT Transactions.[Job Number], Transactions.Asset, Left([First Name],1) & Left([Last Name],1) AS Initials," & _
	" Format([Checked Out Date],"dd/mm/yyyy") AS FCheckOut, IIf([Checked In Date] Is Null, Format([Due Date],"dd/mm/yyyy")," & _
	" Format([Checked In Date],"dd/mm/yyyy")) AS FInDate" & _
	" FROM Transactions LEFT JOIN Contacts ON Transactions.[Checked Out To] = Contacts.ID" & _
	" WHERE (((Transactions.Asset)=[Forms]![Reserve].[Asset]));"
DoCmd.SetWarnings True

Or you can remove the needed for the 2 warning lines by doing this

Code:
CurrentdB.Execute "SELECT Reservations.[Job Number], Reservations.Asset, Left([First Name],1) & Left([Last Name],1) AS Initials," & _
	" Format([Checked Out Date],"dd/mm/yyyy") AS FCheckOut, IIf([Checked In Date] Is Null, Format([Due Date],"dd/mm/yyyy")," & _
	" Format([Checked In Date],"dd/mm/yyyy")) AS FInDate" & _
	" FROM Reservations LEFT JOIN Contacts ON Reservations.[Checked Out To] = Contacts.ID" & _
	" WHERE (((Reservations.Asset)=[Forms]![Reserve]![Asset]))" & _
	" UNION ALL SELECT Transactions.[Job Number], Transactions.Asset, Left([First Name],1) & Left([Last Name],1) AS Initials," & _
	" Format([Checked Out Date],"dd/mm/yyyy") AS FCheckOut, IIf([Checked In Date] Is Null, Format([Due Date],"dd/mm/yyyy")," & _
	" Format([Checked In Date],"dd/mm/yyyy")) AS FInDate" & _
	" FROM Transactions LEFT JOIN Contacts ON Transactions.[Checked Out To] = Contacts.ID" & _
	" WHERE (((Transactions.Asset)=[Forms]![Reserve].[Asset]));"

Hopefully you can see what I've done in the conversion.
Obviously I haven't tested it as I don't have the data

Allan Brownes's sql to vba tool would do this type of thing for you until you feel confident to do it yourself

If the union query doesn't work as a query:
(you've probably already done steps 1 to 4)
1. Split into the 2 parts & run each of these as a query
2. Convert to SQL
3. Rejoin with UNION statement
4. Run the query & test it works
5. Convert to VBA as above

Colin
 
Hi Colin, thank you very much...i put the code you gave me in the VBA (see below) and it highlights it all in red and gives the error below:

Compile Error: Expected end of statement and highlights the first "dd" in the code

Any thoughts.......

Code:
strSql = "SELECT Reservations.[Job Number], Reservations.Asset, Left([First Name],1) & Left([Last Name],1) AS Initials," & _
            " Format([Checked Out Date],"dd/mm/yyyy") AS FCheckOut, IIf([Checked In Date] Is Null, Format([Due Date],"dd/mm/yyyy")," & _
            " Format([Checked In Date],"dd/mm/yyyy")) AS FInDate" & _
            " FROM Reservations LEFT JOIN Contacts ON Reservations.[Checked Out To] = Contacts.ID" & _
            " WHERE (((Reservations.Asset)=[Forms]![Reserve]![Asset]))" & _
            " UNION ALL SELECT Transactions.[Job Number], Transactions.Asset, Left([First Name],1) & Left([Last Name],1) AS Initials," & _
            " Format([Checked Out Date],"dd/mm/yyyy") AS FCheckOut, IIf([Checked In Date] Is Null, Format([Due Date],"dd/mm/yyyy")," & _
            " Format([Checked In Date],"dd/mm/yyyy")) AS FInDate" & _
            " FROM Transactions LEFT JOIN Contacts ON Transactions.[Checked Out To] = Contacts.ID" & _
            " WHERE (((Transactions.Asset)=[Forms]![Reserve].[Asset]));"
 
You'll need to change the "dd/mm/yyyy" to 'dd/mm/yyy' as the double quotes " will escape the string.

Add a Debug.Print strSQL before you execute the query. In the immediate window (Ctrl+G) in the VBA editor you will see the query as it is truing to being executed.
 
i changed the "dd/mm/yyyy" to 'dd/mm/yyyy' on all of these in the code and it now gives an Error Too few parameters. 1 Expected
 
I'm sorry, forgot to wrap the date parts in # strings.
You need #" & in front and & "# afterwards

Try this

Code:
strSql = "SELECT Reservations.[Job Number], Reservations.Asset, Left([First Name],1) & Left([Last Name],1) AS Initials," & _
            " #" & Format([Checked Out Date],"dd/mm/yyyy") & "# AS FCheckOut, IIf([Checked In Date] Is Null, #" & Format([Due Date],"dd/mm/yyyy") & "#," & _
            " #" & Format([Checked In Date],"dd/mm/yyyy")) & "# AS FInDate" & _
            " FROM Reservations LEFT JOIN Contacts ON Reservations.[Checked Out To] = Contacts.ID" & _
            " WHERE (((Reservations.Asset)=[Forms]![Reserve]![Asset]))" & _
            " UNION ALL SELECT Transactions.[Job Number], Transactions.Asset, Left([First Name],1) & Left([Last Name],1) AS Initials," & _
            " #" & Format([Checked Out Date],"dd/mm/yyyy") & "# AS FCheckOut, IIf([Checked In Date] Is Null, #" & Format([Due Date],"dd/mm/yyyy") & "#," & _
            " #" & Format([Checked In Date],"dd/mm/yyyy")) & "# AS FInDate" & _
            " FROM Transactions LEFT JOIN Contacts ON Transactions.[Checked Out To] = Contacts.ID" & _
            " WHERE (((Transactions.Asset)=[Forms]![Reserve].[Asset]));"

If it still doesn't work, do what I said in the numbered list in previous post.

Colin
 
Just read Minty's comments

Code:
You'll need to change the "dd/mm/yyyy" to 'dd/mm/yyy' as the double quotes " will escape the string.

Not true once the #" parts are included
- it will work like I listed the 2nd time PROVIDING the underlying queries are correct in the first place

Code:
Add a Debug.Print strSQL before you execute the query. In the immediate window (Ctrl+G) in the VBA editor you will see the query as it is truing to being executed.

I agree

However, I really suggest you build up the VBA the way I said in the earlier post so you can see how the conversion works

Colin
 
hi ridders....i have added your new code and still get a compile error now it goes to the 3rd "dd/mm/yyyy")) on the second ")"
 
Hi

I'm answering this on my phone so obviously don't have Access available.
I won't be back at my computer till this evening UK time (middle of the night for you).

I can look properly this evening if you upload the latest version or at least just the relevant parts.

However I hope this doesn't sound rude but it would be faster for you to just follow steps 1 to 5 in previous post & have the satisfaction of solving it yourself
 
Ah just noticed

One too many ) in that SQL statement. Apologies
Remove one of them at the point where it failed before.

You'll need to do that again in the same place of the second half of the SQL
 
I am form the UK so time is the same mate.

I have tried everything and have come up with nothing that works. The union query works fine and shows me the records i need but cant seem to get it to work in VBA form.

I have also been looking at it from a different angle which may be easier as i need to then also add a number of WHERE criteria into it and i am not sure i have the skills to do that! I can add the info from the Reservations table into the Transactions table so i just have 1 table removing the need for the Union join which i think makes more sense. I just need to automate the addition when the user is entering the data so that shouldn't be a problem.

The code below shows the SQL which works fine all i need to do on this i think is to add in another where (below) so that it filters out all other records except those that are the same Asset as that selected on the form and then does all the other Wheres already detailed.......Any help on this would be great

Code:
 WHERE (((Transactions.Asset)=[Forms]![Reserve]![Asset]))

Code:
strSql = "Select Transactions.[Job Number], Transactions.Initials, Transactions.FCheckOut, Transactions.FInDate " & _
            "From Transactions Where Transactions.[FCheckOut] " & _
            "Between " & lngFirstOfMonth & " And " & lngLastOfMonth & _
            " or Transactions.[FInDate] Between " & lngFirstOfMonth & " And " & lngLastOfMonth & _
            " or (Transactions.[FCheckOut] < " & lngFirstOfMonth & _
            " and Transactions.[FInDate] > " & lngLastOfMonth & ")" & _
            " ORDER BY Transactions.[FCheckOut], " & _
            "Transactions.[Job Number];"
 
in case you are wondering the full code for the Populate Calendar sub is:

Code:
Private Sub PopulateCalendar()
On Error GoTo Err_PopulateCalendar
Dim strFirstOfMonth As String, bytFirstWeekdayOfMonth As Byte, bytBlockCounter As Byte
Dim bytBlockDayOfMonth As Byte, lngBlockDate As Long, ctlDayBlock As TextBox
Dim bytDaysInMonth As Byte, bytEventDayOfMonth As Byte, lngFirstOfMonth As Long
Dim lngLastOfMonth As Long, lngFirstOfNextMonth As Long, lngLastOfPreviousMonth As Long
Dim lngEventDate As Long, bytBlankBlocksBefore As Byte, bytBlankBlocksAfter As Byte
Dim astrCalendarBlocks(1 To 42) As String, db As DAO.Database, rstEvents As DAO.Recordset
Dim strEvent As String
Dim lngSystemDate As Long
Dim ctlSystemDateBlock As TextBox, blnSystemDateIsShown As Boolean
Dim strSql As String
Dim strSQL1 As String
Dim strSQL2 As String
Dim lngFirstDateInRange As Long
Dim lngLastDateInRange As Long
Dim lngEachDateInRange As Long

lngSystemDate = Date
intMonth = objCurrentDate.Month
intYear = objCurrentDate.Year

lblMonth.Caption = MonthAndYear(intMonth, intYear)
strFirstOfMonth = "1/" & str(intMonth) & "/" & str(intYear)


bytFirstWeekdayOfMonth = WeekDay(strFirstOfMonth)
lngFirstOfMonth = DateSerial(intYear, intMonth, 1)
lngFirstOfNextMonth = DateSerial(intYear, intMonth + 1, 1)
lngLastOfMonth = lngFirstOfNextMonth - 1
lngLastOfPreviousMonth = lngFirstOfMonth - 1
bytDaysInMonth = lngFirstOfNextMonth - lngFirstOfMonth
bytBlankBlocksBefore = bytFirstWeekdayOfMonth - 1
bytBlankBlocksAfter = 42 - (bytBlankBlocksBefore + bytDaysInMonth)
    
Set db = CurrentDb
                      
strSql = "Select Transactions.[Job Number], Transactions.Initials, Transactions.FCheckOut, Transactions.FInDate " & _
            "From Transactions Where Transactions.[FCheckOut] " & _
            "Between " & lngFirstOfMonth & " And " & lngLastOfMonth & _
            " or Transactions.[FInDate] Between " & lngFirstOfMonth & " And " & lngLastOfMonth & _
            " or (Transactions.[FCheckOut] < " & lngFirstOfMonth & _
            " and Transactions.[FInDate] > " & lngLastOfMonth & ")" & _
            " ORDER BY Transactions.[FCheckOut], " & _
            "Transactions.[Job Number];"
                            
Debug.Print strSql
 
Set rstEvents = db.OpenRecordset(strSql)

Do While Not rstEvents.EOF
  
  lngFirstDateInRange = rstEvents![FCheckOut]
  If lngFirstDateInRange < lngFirstOfMonth Then
  lngFirstDateInRange = lngFirstOfMonth
  End If
  lngLastDateInRange = rstEvents![FInDate]
  If lngLastDateInRange > lngLastOfMonth Then
    lngLastDateInRange = lngLastOfMonth
  End If
  
  For lngEachDateInRange = lngFirstDateInRange To lngLastDateInRange
    bytEventDayOfMonth = (lngEachDateInRange - lngLastOfPreviousMonth)
    bytBlockCounter = bytEventDayOfMonth + bytBlankBlocksBefore

      If astrCalendarBlocks(bytBlockCounter) = "" Then
        astrCalendarBlocks(bytBlockCounter) = rstEvents![Job Number] & vbNewLine & rstEvents![Initials]
      Else
        astrCalendarBlocks(bytBlockCounter) = astrCalendarBlocks(bytBlockCounter) & vbNewLine & _
                                              rstEvents![Job Number] & vbNewLine & rstEvents![Initials]
   
      End If
  Next lngEachDateInRange
  
  
    rstEvents.MoveNext
Loop
    
For bytBlockCounter = 1 To 42                       'blank blocks at start of month
  Select Case bytBlockCounter
    Case Is < bytFirstWeekdayOfMonth
      astrCalendarBlocks(bytBlockCounter) = ""
      ReferenceABlock ctlDayBlock, bytBlockCounter
      ctlDayBlock.BackColor = 12632256
      ctlDayBlock = ""
      ctlDayBlock.Enabled = False
      ctlDayBlock.Tag = ""
    Case Is > bytBlankBlocksBefore + bytDaysInMonth 'blank blocks at end of month
      astrCalendarBlocks(bytBlockCounter) = ""
      ReferenceABlock ctlDayBlock, bytBlockCounter
      ctlDayBlock.BackColor = 12632256
      ctlDayBlock = ""
      ctlDayBlock.Enabled = False
      ctlDayBlock.Tag = ""
        ctlDayBlock.Visible = Not (bytBlankBlocksAfter > 6 And bytBlockCounter > 35)
        
    Case Else   'blocks that hold days of the month
      bytBlockDayOfMonth = bytBlockCounter - bytBlankBlocksBefore
      ReferenceABlock ctlDayBlock, bytBlockCounter
      lngBlockDate = lngLastOfPreviousMonth + bytBlockDayOfMonth 'block's date
        If bytBlockDayOfMonth < 10 Then
          ctlDayBlock = Space(2) & bytBlockDayOfMonth & _
                        vbNewLine & astrCalendarBlocks(bytBlockCounter)
        Else
          ctlDayBlock = bytBlockDayOfMonth & _
                        vbNewLine & astrCalendarBlocks(bytBlockCounter)
        End If
                                        
        'If this block is the system date, change its color (CFB 1-25-08)
        If lngBlockDate = lngSystemDate Then
          ctlDayBlock.BackColor = RGB(0, 0, 255)
          ctlDayBlock.ForeColor = QBColor(15)
          Set ctlSystemDateBlock = ctlDayBlock
          blnSystemDateIsShown = True
        Else
          ctlDayBlock.BackColor = QBColor(15)
          ctlDayBlock.ForeColor = 8388608
        End If
          ctlDayBlock.Visible = True
          ctlDayBlock.Enabled = True
          ctlDayBlock.Tag = lngBlockDate
  End Select
Next

Call PopulateYearListBox

Exit_PopulateCalendar:
  Exit Sub
Err_PopulateCalendar:
  MsgBox Err.Description, vbExclamation, "Error inPopulateCalendar()"
  Call LogErrors(Err.Number, Err.Description, "frmCalendar", "PopulateCalendar() Sub-Routine", "Called from Multiple Locations")
    Resume Exit_PopulateCalendar
End Sub
 
any help on the above any one!?! I need to add in the WHERE below:

Code:
WHERE (((Transactions.Asset)=[Forms]![Reserve]![Asset]))

Into the strSQL. There are a number of ANDs and OR's so i just cant get the brackets correct. I think there needs to be 3 of the above in the statement below one for each where argument...

Code:
strSql = "Select Transactions.[Job Number], Transactions.Initials, Transactions.FCheckOut, Transactions.FInDate " & _
            "From Transactions Where Transactions.[FCheckOut] " & _
            "Between " & lngFirstOfMonth & " And " & lngLastOfMonth & _
            " or Transactions.[FInDate] Between " & lngFirstOfMonth & " And " & lngLastOfMonth & _
            " or (Transactions.[FCheckOut] < " & lngFirstOfMonth & _
            " and Transactions.[FInDate] > " & lngLastOfMonth & ")" & _
            " ORDER BY Transactions.[FCheckOut], " & _
            "Transactions.[Job Number];"

This is what i have so far but I cant get it to work, Im lost on all the brackets and &'s lol......should give you an idea of what i need though

Code:
strSql = "Select Transactions.[Job Number], Transactions.Initials, Transactions.FCheckOut, Transactions.FInDate " & _
            "From Transactions Where (((Transactions.Asset)=[Forms]![Reserve]![Asset]) AND (Transactions.[FCheckOut] " & _
            "Between " & lngFirstOfMonth & " And " & lngLastOfMonth & "))" & _
            " or (((Transactions.Asset)=[Forms]![Reserve]![Asset]) AND (Transactions.[FInDate] Between " & lngFirstOfMonth & " And " & lngLastOfMonth & ")" & _
            " or (((Transactions.Asset)=[Forms]![Reserve]![Asset]) AND (Transactions.[FCheckOut] < " & lngFirstOfMonth & ")" & _
            " and ((Transactions.[FInDate]) > " & lngLastOfMonth & "))" & _
            " ORDER BY Transactions.[FCheckOut], " & _
            "Transactions.[Job Number];"

:banghead:
 
Whoa, slow down a bit. Let me try & fix the union query first.
I've not looked at the detail of your last 2 posts

Sorry about Japan comment - was mixing you up with another post I was dealing with

Now I'm back on a computer rather than a tablet, I can see you did need 2 ) brackets on the 3rd date field after all. However not where you had put them! As you had an IIf statement that bit should be:

Code:
IIf([Checked In Date] Is Null, #" & Format([Due Date],"dd/mm/yyyy") & "#, #" & Format([Checked In Date],"dd/mm/yyyy") & "#) AS FInDate

I've tried re-constructing this here but really need a stripped down version of your db to test it properly.

As far as I can see I need 3 tables, Reservations, Transactions & Contacts together with the original union query and the form called Reserve.

Obviously do remove any sensitive data but leave enough records for me to see what you're expecting to see

Also you really should avoid having spaces in field names as it can cause real problems. So for example use CheckedInDate, DueDate, CheckedOutDate, LastName (or Surname) etc

Colin
 
Hi Colin.....lol ive been working on it for days and days lol hence me looking at other options.

My concern is the Union bit is only the first bit and then i would need to add in all the And / Or statements lol which i just dont have the capability to do lol
 

Users who are viewing this thread

Back
Top Bottom