Dlookup returning trimmed text

[txgeekgirl]

Hi all - I have a field in a table that is storing a number as text. In the table - the number is correct as 0365. However, on a Dlookup it comes up 365. I tried forcing a "0" in front and it still comes up 365.

Is there a trick to this. It has to match on a query.

 

[spikepl]

I am curious - how can you tell exactly what Dlookup comes up with? Do you save the value in some varaible? If so of what type? How exactly did you attempt to "force" a 0 in front?

 

[Galaxiom]

Are you sure the table is storing a number as text? It is behaving more like a number type with a Format property of 0000.

 

[txgeekgirl]

You will not believe the stupid thing I did....

I left work and came back after everyone had gone, looked at the code, back through the variable declarations and I had made them doubles instead of string probably 4 months ago and never ever even thought to double check them.


I also moved the variables to the sub... Here's the code... It's making changes on another server DB and then at the end sends all the info in an email to that servers folder.

Sub CopyCareRptDB(IdentRecordFromRequest)
    Dim OldStaffID As String
    Dim NewStaffID As String
    Dim mysql As String
    
    Dim rs As Object
    Dim con As Object
        
    OldStaffID = DLookup("[COPY_CareReports]", "[NewStaffRequests]", "[ID] = " & IdentRecordFromRequest)
    NewStaffID = DLookup("[StaffID]", "[NewStaffRequests]", "[ID] = " & IdentRecordFromRequest)
    'flush table
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "Flush_CareReportCopy"
    DoCmd.SetWarnings True
       
   'copy Care Reports
    mysql = "INSERT INTO CareReportCopy ( [Report Number])"
    mysql = mysql & " SELECT [Care Report Staff XRef].[Report Number]"
    mysql = mysql & " From [Care Report Staff XRef] WHERE [Care Report Staff XRef].[Staff ID]= '" & OldStaffID & "';"
    DoCmd.SetWarnings False
    DoCmd.RunSQL mysql
    DoCmd.SetWarnings True
    'Add new records
    mysql = "INSERT INTO [Care Report Staff XRef] ( [Report Number], [Staff ID] ) "
    mysql = mysql + "SELECT CareReportCopy.[Report Number] AS Reportnum, '" & Trim(Str(NewStaffID)) & "' AS StaffID "
    mysql = mysql + "FROM CareReportCopy;"
    
    DoCmd.SetWarnings False
    DoCmd.RunSQL mysql
    DoCmd.SetWarnings True
    
    AddEmailToCRS (IdentRecordFromRequest)
End Sub

 

[Galaxiom]

You will not believe the stupid thing I did....

Yes I would. We would all have stories of spending hours investigating some wieird behaviour and going home wondering "what the hell" only to come back in the morning and have the bl00dy obvious staring us in the face.

 

[Galaxiom]

BTW Something quite useful but often overlooked is "Quick Info" the context menu in the VBE.

Quick Info on the variable will popup its declared scope and type. (Rightclick and Q for less mousing). Saves having to find the delcaration or use Hungarian Notation to remind you.

 

[txgeekgirl]

I am Hungarian - 1/2 at least LOL!

That is cool - thanks. I have been coding in VBA for YEARSSSSSSS and never even knew that. Very Cool!