Access rs to Excel sheet

[Welshgasman]

Any data in the recordset as you are not formatting the date?

[DMT Dave]

I can't say it enough, you are all a great on here, although it should be just about automatic for me, i still get lost wit hit sometimes, i could do with a step by number of sequence and keep hold of it

ie:
1 Set XL
2 Set WB
3 set WS
4 Set RS
5 Do Until
6 XL Row Count

etc etc...,

all working good

I sometimes wonder, difficult things in life are easy and easy things are difficult

Working Code

 iCondition = InputBox("Enter The Condition Of Your Images ?" & vbCrLf & vbCrLf & _        
        Chr(149) & " 1 " & Chr(149) & "Good, No Visible Damage" & vbCrLf & vbCrLf & _
        Chr(149) & " 2 " & Chr(149) & "Good, Packaging Damage" & vbCrLf & vbCrLf & _
        Chr(149) & " 3 " & Chr(149) & "Marked, Very Light Damage" & vbCrLf & vbCrLf & _
        Chr(149) & " 4 " & Chr(149) & "Marked, Heavy Damage", "ENTER CONDITION", "1")
        Select Case iCondition
            Case Is = 1
                sDamage = "Good, No Visible Damage"
            Case Is = 2
                sDamage = "Good, Packaging Damage"
            Case Is = 3
                sDamage = "Marked, Very Light Damage"
            Case Is = 4
                sDamage = "Marked, Heavy Damage"
        End Select
        
    strPC = "Customer PostCode Removed"
 
    pOpen = "T:\DMT Ltd\Images\Stored Images\Images\Customer Name Folder Removed\"
    fOpen = "Condition Report.xlsx"
    
    Set apXL = CreateObject("Excel.Application")
    Set xlWB = apXL.Workbooks.Open(pOpen & fOpen)
    Set xlWS = xlWB.Worksheets("Sheet1")
    apXL.Visible = False
    
    dtShipDate = Forms!frmMainMenu!frmIndex1!cboShipmentDateIndex1
    
    Set rs = CurrentDb.OpenRecordset("Select * From tblEdit WHERE ShipmentDate = #" & Format(dtShipDate, "mm/dd/yyyy") & "# And PostCode = '" & strPC & "'")
    
    With xlWS
        
        intLR = .Cells(.Rows.Count, 1).End(xlUp).Row
        
        Do Until rs.EOF
            
            strSL = rs.Fields("SONumber")
            strMFG = rs.Fields("ItemNo")
            dtShipDate = rs.Fields("ShipmentDate")
            sDelTo = rs.Fields("DelTo")
            strPostCode = rs.Fields("PostCode")
         
        xlWS.Cells(intLR + 1, 1) = Format(dtShipDate, "dd-mm-yyyy")
        xlWS.Cells(intLR + 1, 2) = sDelTo
        xlWS.Cells(intLR + 1, 3) = strPostCode
        xlWS.Cells(intLR + 1, 4) = strSL
        xlWS.Cells(intLR + 1, 5) = strMFG
        xlWS.Cells(intLR + 1, 6) = sDamage
        xlWS.Cells.EntireColumn.HorizontalAlignment = xlLeft
             
        rs.MoveNext
        
        intLR = intLR + 1
        
        Loop
        
        xlWB.Save
        xlWB.Close
        apXL.Quit
        
    End With
       
    Debug.Print strSQL
    
Set apXL = Nothing




Application.FollowHyperlink pOpen & fOpen

The only part on the excel result is the date is USA mm/dd/yyyy, sometimes setting the initial variable with dates in, i find it works better to format mm/dd/yyyy in the declared variable then on any excel files / reports / forms / query results change back to dd/mm/yyyy for UK dates

However you are all legends (this is not leg-ends) end of your legs

Result:



Finally I think my next task with this is what if 1 item is damaged and 1 is not so then i would write an adjust option and have similar code to load into recordset just the Sales Order (SONumber) within the same shipment date

Thanks again guys

[DMT Dave]

Finally I think my next task with this is what if 1 item is damaged and 1 is not so then i would write an adjust option and have similar code to load into recordset just the Sales Order (SONumber) within the same shipment date

Thanks again guys


Or i set another input box asking if all items are not damaged then select case and add which one or edit in another event procedure

[Welshgasman]

Dave,
Real dates are just numbers.
You need the format your date for the sql string so Access can find the correct date, but the date should still be in your regional settings, which is UK.
So DO NOT format the date for the cell, just in the sql string.

Plus if you use Format() for that cell, you have now changed a date into a string.

[DMT Dave]

Thanks WGM, so just to confirm, are you referring to:

  dtShipDate = Forms!frmMainMenu!frmIndex1!cboShipmentDateIndex1    
    Set rs = CurrentDb.OpenRecordset("Select * From tblEdit WHERE ShipmentDate = #" & Format(dtShipDate, "mm/dd/yyyy") & "# And PostCode = '" & strPC & "'")
    
    With xlWS
        
        intLR = .Cells(.Rows.Count, 1).End(xlUp).Row
        
        Do Until rs.EOF
            
            strSL = rs.Fields("SONumber")
            strMFG = rs.Fields("ItemNo")
            dtShipDate = Format(rs.Fields("ShipmentDate"), "dd-mmm-yyyy")
            sDelTo = rs.Fields("DelTo")
            strPostCode = rs.Fields("PostCode")
         
        xlWS.Cells(intLR + 1, 1) = dtShipDate
        xlWS.Cells(intLR + 1, 2) = sDelTo
        xlWS.Cells(intLR + 1, 3) = strPostCode
        xlWS.Cells(intLR + 1, 4) = strSL
        xlWS.Cells(intLR + 1, 5) = strMFG
        xlWS.Cells(intLR + 1, 6) = sDamage
        xlWS.Cells.EntireColumn.HorizontalAlignment = xlLeft

And Not:

  dtShipDate = Forms!frmMainMenu!frmIndex1!cboShipmentDateIndex1    
    Set rs = CurrentDb.OpenRecordset("Select * From tblEdit WHERE ShipmentDate = #" & Format(dtShipDate, "mm/dd/yyyy") & "# And PostCode = '" & strPC & "'")
    
    With xlWS
        
        intLR = .Cells(.Rows.Count, 1).End(xlUp).Row
        
        Do Until rs.EOF
            
            strSL = rs.Fields("SONumber")
            strMFG = rs.Fields("ItemNo")
            dtShipDate = Format(rs.Fields("ShipmentDate"), "dd-mmm-yyyy")
            sDelTo = rs.Fields("DelTo")
            strPostCode = rs.Fields("PostCode")
         
        xlWS.Cells(intLR + 1, 1) = dtShipDate
        xlWS.Cells(intLR + 1, 2) = sDelTo
        xlWS.Cells(intLR + 1, 3) = strPostCode
        xlWS.Cells(intLR + 1, 4) = strSL
        xlWS.Cells(intLR + 1, 5) = strMFG
        xlWS.Cells(intLR + 1, 6) = sDamage
        xlWS.Cells.EntireColumn.HorizontalAlignment = xlLeft

?

[DMT Dave]

Or how would you set the date if not by Format() ?

IN this instance, i am better setting the Excel column date setting ?

[Welshgasman]

Ok, let's take a step back.
In your first block of code, you format the date from the recordset. So it becomes a string?
Now, wherever you use that variable you will be inserting a string value, that looks like a date.

Now if you DO NOT format that date from the recordset, and leave it as it is, (a s you did at the start. Did you not look at your sheet then?), it will remain a date.
Now if you assign that to a cell, it will remain a date, and so will respect your regional settings.

However, your question highlights the fact that you are not understanding the code you are writing?

No need to set the date, unless you do not want it as per your system. We in the UK need to follow Access's requirement to supply dates in either mm/dd/yyyy or yyyy-mm-dd format.
Once you can understand why, it is not that hard to work with dates in Access.

[DMT Dave]

Thanks WGM