3rd grade code returns extra zero instead of decimal or hides deci

B

Barry A&P

I scabbed together some code to automatically populate some form controls
based on what info it can find in the database, its goofy because some
records are not complete so i want to use what is usable.. My method may
be a little hacked so i would also appreciate any input on cleaning it up..

my table T_Installhistory has installation and removal data for a component,
the code finds the record for that component (By SNID) then Finds the Dmax
Removaldate i want everything else to refer to this record.. anyway my
issue is when the TSO and TSN boxes are populated on the form the numbers
have an extra zero on the end so i get 12340 instead of the correct 1234.0 i
tried changing the control to Fixed with 1 decimal but no luck.. the values
need to come from. the most recent removal date and matching SNID..

the math is RemovalParentTT - installParentTT (total Time) = TIS (time in
service) then installTSO + TIS = TSO and Install TSN + TIS = TSN (time
since New)

Where does my decimal point dissappear to ?
Maybe my use of strings is an issue

Here is my code
Private Sub SerialNumberIDCombo_AfterUpdate()
'Set all my variables
Dim RmvDate As String
Dim InstallID As String
Dim InstallTSN As String
Dim InstallTSO As String
Dim InstallparentTT As String
Dim RemovalParentTT As String
Dim TotalTIS As String
Dim ParentSNID As String
Dim Remarks As String 'string to fill remarks area on tag
Dim Reason As String

'Clear some stuff and fill what i can

Me!NSN = Me!SerialNumberIDCombo.Column(1)
Me!PartNumber = Me!SerialNumberIDCombo.Column(2)
Me!Description = Me!SerialNumberIDCombo.Column(3)
Me!SerialNumber = Me!SerialNumberIDCombo.Column(4)
Remarks = Me!SerialNumberIDCombo.Column(5)
Me!RemovedFrom = ""
Me!AtTT = ""
Me!TSO = ""
Me!TSN = ""
Me!TagDate = Date


'quit if there is no removal date
RmvDate = Nz(DMax("[RemovalDate]", "T_installhistory", "SerialNumberID = " &
Me![SerialNumberIDCombo]), "#0#")
If RmvDate = "#0#" Then
MsgBox "No removal history found for this item..", , "No History
Available."
Me!SNNotes = Remarks
Exit Sub
End If

'show msg if the max removal date is less than the max install date
If DMax("[InstallDate]", "T_installhistory", "SerialNumberID = " &
Me![SerialNumberIDCombo]) >= RmvDate Then
MsgBox "My Records show a Installation date posted after this removal
date. This tag may not have the most current data.", , "Warning!! Is
Component Installed?"
End If

'If the dates are good lets fill out some more data
InstallID = DLookup("[installID]", "T_installhistory", "SerialNumberID = " &
Me![SerialNumberIDCombo] & " AND RemovalDate = #" & RmvDate & "#")
InstallTSN = Nz(DLookup("[installTSN]", "T_installhistory", "installID = " &
InstallID), "UNK")
InstallTSO = Nz(DLookup("[InstallTSO]", "T_installhistory", "installID = " &
InstallID), "UNK")
RemovalParentTT = Nz(DLookup("[RemovalParentTT]", "T_installhistory",
"installID = " & InstallID), "UNK")
InstallparentTT = Nz(DLookup("[InstallParentTT]", "T_installhistory",
"installID = " & InstallID), "UNK")
ParentSNID = Nz(DLookup("[ParentID]", "T_installhistory", "installID = " &
InstallID), "UNK")
Me!RemovedFrom = Nz(DLookup("[SerialNumber]", "T_Serialnumbers",
"SerialnumberID = " & ParentSNID), "UNK")
Me!AtTT = RemovalParentTT

'add a reason to our remarks string
If IsNull(DLookup("[ReasonForRemoval]", "T_installhistory", "installID =
" & InstallID)) Then
Remarks = "Removed: " & RmvDate & " " & Remarks
Else
Remarks = "Removed: " & RmvDate & " Reason: " &
DLookup("[ReasonForRemoval]", "T_installhistory", "installID = " & InstallID)
& " " & Remarks
End If

'We dont want to have bad data
If InstallparentTT = "UNK" Or RemovalParentTT = "UNK" Then
Me!SNNotes = Remarks
Exit Sub
End If

TotalTIS = RemovalParentTT - InstallparentTT
Me!AtTT = RemovalParentTT

'we also cant have a negative Time in service
If TotalTIS < 0 Then
Me!SNNotes = Remarks
Exit Sub
End If

If InstallTSO = "UNK" Then
Me!SNNotes = Remarks
Exit Sub
End If

Me!TSO = InstallTSO + TotalTIS

If InstallTSN = "UNK" Then
Me!SNNotes = Remarks
Exit Sub
End If

Me!TSN = InstallTSN + TotalTIS

'lets fineally fill that remarks box
Me!SNNotes = Remarks
End Sub

Thanks for your help

Barry
 
B

Barry A&P

I discovered the issue is with how i declared my variables because they are
all strings when i used the + operator access concatenates two strings rather
than add there values so this line "Me!TSO = InstallTSO + TotalTIS" was
returning 12340 and 0.0 concatenated to 123400

If i change all my times to singles i get errors because of my nz( ,"UNK")
's but i am not sure of a better way to check for nulls??

if i just change the totalTIS to a single it works but i feel i am covering
up a greater issue with my code..

Any help with the below code would be greatly appreciated..

Barry A&P said:
I scabbed together some code to automatically populate some form controls
based on what info it can find in the database, its goofy because some
records are not complete so i want to use what is usable.. My method may
be a little hacked so i would also appreciate any input on cleaning it up..

my table T_Installhistory has installation and removal data for a component,
the code finds the record for that component (By SNID) then Finds the Dmax
Removaldate i want everything else to refer to this record.. anyway my
issue is when the TSO and TSN boxes are populated on the form the numbers
have an extra zero on the end so i get 12340 instead of the correct 1234.0 i
tried changing the control to Fixed with 1 decimal but no luck.. the values
need to come from. the most recent removal date and matching SNID..

the math is RemovalParentTT - installParentTT (total Time) = TIS (time in
service) then installTSO + TIS = TSO and Install TSN + TIS = TSN (time
since New)

Where does my decimal point dissappear to ?
Maybe my use of strings is an issue

Here is my code
Private Sub SerialNumberIDCombo_AfterUpdate()
'Set all my variables
Dim RmvDate As String
Dim InstallID As String
Dim InstallTSN As String
Dim InstallTSO As String
Dim InstallparentTT As String
Dim RemovalParentTT As String
Dim TotalTIS As String
Dim ParentSNID As String
Dim Remarks As String 'string to fill remarks area on tag
Dim Reason As String

'Clear some stuff and fill what i can

Me!NSN = Me!SerialNumberIDCombo.Column(1)
Me!PartNumber = Me!SerialNumberIDCombo.Column(2)
Me!Description = Me!SerialNumberIDCombo.Column(3)
Me!SerialNumber = Me!SerialNumberIDCombo.Column(4)
Remarks = Me!SerialNumberIDCombo.Column(5)
Me!RemovedFrom = ""
Me!AtTT = ""
Me!TSO = ""
Me!TSN = ""
Me!TagDate = Date


'quit if there is no removal date
RmvDate = Nz(DMax("[RemovalDate]", "T_installhistory", "SerialNumberID = " &
Me![SerialNumberIDCombo]), "#0#")
If RmvDate = "#0#" Then
MsgBox "No removal history found for this item..", , "No History
Available."
Me!SNNotes = Remarks
Exit Sub
End If

'show msg if the max removal date is less than the max install date
If DMax("[InstallDate]", "T_installhistory", "SerialNumberID = " &
Me![SerialNumberIDCombo]) >= RmvDate Then
MsgBox "My Records show a Installation date posted after this removal
date. This tag may not have the most current data.", , "Warning!! Is
Component Installed?"
End If

'If the dates are good lets fill out some more data
InstallID = DLookup("[installID]", "T_installhistory", "SerialNumberID = " &
Me![SerialNumberIDCombo] & " AND RemovalDate = #" & RmvDate & "#")
InstallTSN = Nz(DLookup("[installTSN]", "T_installhistory", "installID = " &
InstallID), "UNK")
InstallTSO = Nz(DLookup("[InstallTSO]", "T_installhistory", "installID = " &
InstallID), "UNK")
RemovalParentTT = Nz(DLookup("[RemovalParentTT]", "T_installhistory",
"installID = " & InstallID), "UNK")
InstallparentTT = Nz(DLookup("[InstallParentTT]", "T_installhistory",
"installID = " & InstallID), "UNK")
ParentSNID = Nz(DLookup("[ParentID]", "T_installhistory", "installID = " &
InstallID), "UNK")
Me!RemovedFrom = Nz(DLookup("[SerialNumber]", "T_Serialnumbers",
"SerialnumberID = " & ParentSNID), "UNK")
Me!AtTT = RemovalParentTT

'add a reason to our remarks string
If IsNull(DLookup("[ReasonForRemoval]", "T_installhistory", "installID =
" & InstallID)) Then
Remarks = "Removed: " & RmvDate & " " & Remarks
Else
Remarks = "Removed: " & RmvDate & " Reason: " &
DLookup("[ReasonForRemoval]", "T_installhistory", "installID = " & InstallID)
& " " & Remarks
End If

'We dont want to have bad data
If InstallparentTT = "UNK" Or RemovalParentTT = "UNK" Then
Me!SNNotes = Remarks
Exit Sub
End If

TotalTIS = RemovalParentTT - InstallparentTT
Me!AtTT = RemovalParentTT

'we also cant have a negative Time in service
If TotalTIS < 0 Then
Me!SNNotes = Remarks
Exit Sub
End If

If InstallTSO = "UNK" Then
Me!SNNotes = Remarks
Exit Sub
End If

Me!TSO = InstallTSO + TotalTIS

If InstallTSN = "UNK" Then
Me!SNNotes = Remarks
Exit Sub
End If

Me!TSN = InstallTSN + TotalTIS

'lets fineally fill that remarks box
Me!SNNotes = Remarks
End Sub

Thanks for your help

Barry
Click to expand...
 
S

Steve Sanford

Yes, declaring variables as strings when they are dates and numbers will
create bad problems (as you found out).

Not knowing the data types (because all variables are declared as strings)
makes it difficult to understand what you are trying to accomplish. Some
examples of the date and expected results is a greatl help.

The DlookUp() function is very slow. If nothing else, I would use ELookup();
see "Extended DLookup()" at

http://allenbrowne.com/ser-42.html



Also, it is hard to answer your questions because it is not clear how your
tables are structured. It appears (to me) that you have committed
"spreadsheet" (been there/ done that ). :(

If you can, take some time to try normalizing your tables. If you are too
far along, provide some examples of input and expected results. I'm sure your
code can be modified to meet your requirements.


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


Barry A&P said:
I discovered the issue is with how i declared my variables because they are
all strings when i used the + operator access concatenates two strings rather
than add there values so this line "Me!TSO = InstallTSO + TotalTIS" was
returning 12340 and 0.0 concatenated to 123400

If i change all my times to singles i get errors because of my nz( ,"UNK")
's but i am not sure of a better way to check for nulls??

if i just change the totalTIS to a single it works but i feel i am covering
up a greater issue with my code..

Any help with the below code would be greatly appreciated..

Barry A&P said:
I scabbed together some code to automatically populate some form controls
based on what info it can find in the database, its goofy because some
records are not complete so i want to use what is usable.. My method may
be a little hacked so i would also appreciate any input on cleaning it up..

my table T_Installhistory has installation and removal data for a component,
the code finds the record for that component (By SNID) then Finds the Dmax
Removaldate i want everything else to refer to this record.. anyway my
issue is when the TSO and TSN boxes are populated on the form the numbers
have an extra zero on the end so i get 12340 instead of the correct 1234.0 i
tried changing the control to Fixed with 1 decimal but no luck.. the values
need to come from. the most recent removal date and matching SNID..

the math is RemovalParentTT - installParentTT (total Time) = TIS (time in
service) then installTSO + TIS = TSO and Install TSN + TIS = TSN (time
since New)

Where does my decimal point dissappear to ?
Maybe my use of strings is an issue

Here is my code
Private Sub SerialNumberIDCombo_AfterUpdate()
'Set all my variables
Dim RmvDate As String
Dim InstallID As String
Dim InstallTSN As String
Dim InstallTSO As String
Dim InstallparentTT As String
Dim RemovalParentTT As String
Dim TotalTIS As String
Dim ParentSNID As String
Dim Remarks As String 'string to fill remarks area on tag
Dim Reason As String

'Clear some stuff and fill what i can

Me!NSN = Me!SerialNumberIDCombo.Column(1)
Me!PartNumber = Me!SerialNumberIDCombo.Column(2)
Me!Description = Me!SerialNumberIDCombo.Column(3)
Me!SerialNumber = Me!SerialNumberIDCombo.Column(4)
Remarks = Me!SerialNumberIDCombo.Column(5)
Me!RemovedFrom = ""
Me!AtTT = ""
Me!TSO = ""
Me!TSN = ""
Me!TagDate = Date


'quit if there is no removal date
RmvDate = Nz(DMax("[RemovalDate]", "T_installhistory", "SerialNumberID = " &
Me![SerialNumberIDCombo]), "#0#")
If RmvDate = "#0#" Then
MsgBox "No removal history found for this item..", , "No History
Available."
Me!SNNotes = Remarks
Exit Sub
End If

'show msg if the max removal date is less than the max install date
If DMax("[InstallDate]", "T_installhistory", "SerialNumberID = " &
Me![SerialNumberIDCombo]) >= RmvDate Then
MsgBox "My Records show a Installation date posted after this removal
date. This tag may not have the most current data.", , "Warning!! Is
Component Installed?"
End If

'If the dates are good lets fill out some more data
InstallID = DLookup("[installID]", "T_installhistory", "SerialNumberID = " &
Me![SerialNumberIDCombo] & " AND RemovalDate = #" & RmvDate & "#")
InstallTSN = Nz(DLookup("[installTSN]", "T_installhistory", "installID = " &
InstallID), "UNK")
InstallTSO = Nz(DLookup("[InstallTSO]", "T_installhistory", "installID = " &
InstallID), "UNK")
RemovalParentTT = Nz(DLookup("[RemovalParentTT]", "T_installhistory",
"installID = " & InstallID), "UNK")
InstallparentTT = Nz(DLookup("[InstallParentTT]", "T_installhistory",
"installID = " & InstallID), "UNK")
ParentSNID = Nz(DLookup("[ParentID]", "T_installhistory", "installID = " &
InstallID), "UNK")
Me!RemovedFrom = Nz(DLookup("[SerialNumber]", "T_Serialnumbers",
"SerialnumberID = " & ParentSNID), "UNK")
Me!AtTT = RemovalParentTT

'add a reason to our remarks string
If IsNull(DLookup("[ReasonForRemoval]", "T_installhistory", "installID =
" & InstallID)) Then
Remarks = "Removed: " & RmvDate & " " & Remarks
Else
Remarks = "Removed: " & RmvDate & " Reason: " &
DLookup("[ReasonForRemoval]", "T_installhistory", "installID = " & InstallID)
& " " & Remarks
End If

'We dont want to have bad data
If InstallparentTT = "UNK" Or RemovalParentTT = "UNK" Then
Me!SNNotes = Remarks
Exit Sub
End If

TotalTIS = RemovalParentTT - InstallparentTT
Me!AtTT = RemovalParentTT

'we also cant have a negative Time in service
If TotalTIS < 0 Then
Me!SNNotes = Remarks
Exit Sub
End If

If InstallTSO = "UNK" Then
Me!SNNotes = Remarks
Exit Sub
End If

Me!TSO = InstallTSO + TotalTIS

If InstallTSN = "UNK" Then
Me!SNNotes = Remarks
Exit Sub
End If

Me!TSN = InstallTSN + TotalTIS

'lets fineally fill that remarks box
Me!SNNotes = Remarks
End Sub

Thanks for your help

Barry
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

is there a Dlookup to get entire record or multiple fields ?? 8

Top