Wierd type mismatch

[poporacer]

I have a form with several unbound controls (Textboxes and checkboxes). The controls get populated when the user selects some items in a cascading listbox. Some of the controls will not have any data. Then there are variables set to check if the user has made changes. I have the variables declared but when I try to put the data in the temp variables, I get a type mismatch. What is wierd is that I have several variables set to the same type and the textboxes are the same. Here is the code:

Private Sub lstCDCNum_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
Dim TempConvDate, TempDaAccept, TempDaRefer, TempDaRej, TempDismiss, TempNotGuilty As Date
Dim TempISUDeny, TempNextCourt As Date
Dim TempCourtComm, TempMental, TempPenal, TempOffense, TempComment, TempDACase As String
Dim TempEscape, TempHomicide, TempIndecent, TempInmateAssault, TempOther, TempDrug As Boolean
Dim TempWeapon, TempSexAssault, TempStaffAssault As Boolean

Set rs = Me.RecordsetClone

rs.FindFirst "[ID] = " & Str(Nz(Me![lstCDCNum], 0))
'Populate Textboxes
Me.txtIncidentDate = Me.lstCDCNum.Column(8)
Me.txtCDCNum = Me.lstCDCNum.Column(1)
Me.txtLName = Me.lstCDCNum.Column(2)
Me.txtEthnic = Me.lstCDCNum.Column(4)
Me.txtCII = Me.lstCDCNum.Column(6)
Me.txtDOB = Me.lstCDCNum.Column(3)
Me.txtFBI = Me.lstCDCNum.Column(5)
Me.txtCommitment = Me.lstCDCNum.Column(7)
Me.txtConvictDate = Nz(rs![Conviction Date], "")
Me.txtCourtComments = Nz(rs![CourtComments], "")
Me.txtDAAccept = Nz(rs![Date DA Accepted], "")
Me.txtDACaseNum = Nz(rs![DA Case Number], "")
Me.txtDARefer = Nz(rs![Date DA Referred], "")
Me.txtDAReject = Nz(rs![Date DA Rejected], "")
Me.txtDismissDate = Nz(rs![Dismissed date], "")
Me.txtNotGuiltyDate = Nz(rs![Not Guilty Date], "")
Me.txtISUDeny = Nz(rs![ISU Denied Date], "")
Me.cmbMentalHealth = Nz(rs![MentalHealth], "")
Me.txtNextCourt = Nz(rs![NextCourtDate], "")
Me.txtPenalCode = Nz(rs![Penal Code], "")
Me.txtSpecificOffense = Nz(rs![Specific Offense], "")
Me.txtComments = Nz(rs![Comments], "")
Me.chkEscape = Nz(rs![Escape], "")
Me.chkHomicide = Nz(rs![Homicide], "")
Me.chkIndecentExp = Nz(rs![Indecent Exposure], "")
Me.chkInmateAssault = Nz(rs![Inmate Assault], "")
Me.chkOther = Nz(rs![Other], "")
Me.chkPossessionDrugs = Nz(rs![Drug], "")
Me.chkPossessionWeapon = Nz(rs![Weapon], "")
Me.chkSexualAssault = Nz(rs![Sexual Assault], "")
Me.chkStaffAssault = Nz(rs![Staff Assault], "")

Set rs = Nothing
'Save textbox info to see if it has changed
TempConvDate = Nz(Me.txtConvictDate, "")
TempCourtComm = Me.txtCourtComments
TempDaAccept = Me.txtDAAccept
TempDACase = Me.txtDACaseNum
TempDaRefer = Me.txtDARefer
TempDaRej = Me.txtDAReject
TempDismiss = Me.txtDismissDate
TempNotGuilty = Me.txtNotGuiltyDate Type mismatch here
TempISUDeny = Me.txtISUDeny
TempMental = Me.cmbMentalHealth
TempNextCourt = Me.txtNextCourt
TempPenal = Me.txtPenalCode
TempOffense = Me.txtSpecificOffense
TempComment = Me.txtComments
TempEscape = Me.chkEscape
TempHomicide = Me.chkHomicide
TempIndecent = Me.chkIndecentExp
TempInmateAssault = Me.chkInmateAssault
TempOther = Me.chkOther
TempDrug = Me.chkPossessionDrugs
TempWeapon = Me.chkPossessionWeapon
TempSexAssault = Me.chkSexualAssault
TempStaffAssault = Me.chkStaffAssault

End Sub

 

[ajetrumpet]

Private Sub lstCDCNum_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
Dim TempConvDate, TempDaAccept, TempDaRefer, TempDaRej, TempDismiss, TempNotGuilty As Date <--- Variable in question
Dim TempISUDeny, TempNextCourt As Date <--- same type of declaration here
Dim TempCourtComm, TempMental, TempPenal, TempOffense, TempComment, TempDACase As String
Dim TempEscape, TempHomicide, TempIndecent, TempInmateAssault, TempOther, TempDrug As Boolean
Dim TempWeapon, TempSexAssault, TempStaffAssault As Boolean

Me.txtNotGuiltyDate = Nz(rs![Not Guilty Date], "") <--- could the problem be occurring here???

Me.txtNextCourt = Nz(rs![NextCourtDate], "") <--- here is another "date" data type control being assigned a value



TempNotGuilty = Me.txtNotGuiltyDate Type mismatch here (is that because it is a zero-length string, I wonder??)

TempNextCourt = Me.txtNextCourt <--- everything is OK with this variable assignment??? What's the diff. between this one, and the "TempNotGuilty" var???

have you seen the following link....????

http://www.access-programmers.co.uk/forums/showthread.php?t=147019

 

[pbaldy]

What is wierd is that I have several variables set to the same type and the textboxes are the same.

Not as weird as you think. You've declared 5 Variants (which can accept a null) and 1 Date (which can't):

Dim TempConvDate, TempDaAccept, TempDaRefer, TempDaRej, TempDismiss, TempNotGuilty As Date

Each variable must be explicitly declared, or it becomes a Variant.

 

[missinglinq]

Paul is absolutely correct, only the last variables named is dimmed as a Date, so TempDismiss is actually dimmed as a Variant. The other thing you have to understand is that VBA frequently doesn't highlight the line causing the error, but rather the line following the line causing the error! Hence the bold line

TempDismiss = Me.txtDismissDate
TempNotGuilty = Me.txtNotGuiltyDate 'Type mismatch here

is actually the culprit.

 

[poporacer]

Ok I changed the declaration to declare each one and I still have the problem...It does appear to be zero length strings, but it works on some and not on others. It doesn't appear to be the line before where the error is indicated. Any Ideas? I have posted the DB...To generate the error, click on an item in the Log # and then click on a name in the other listbox.

 

[pbaldy]

Well, you've declared the variable as a Date, and as you suspect a Date can not accept a ZLS. I haven't tried to follow the process you're trying to achieve, so you either need to make the variables Variants so they can accept whatever comes along, or prevent Null and ZLS values from getting to them.

 

[poporacer]

Thanks, after running through the routine a few times I see that the error was with the ZLS in the date variable...a couple of questions on that note...can date be null? And if I use a Variant type, what downfalls might I expect down the road? For example, saving a variant null or ZLS into a date field, calculating number of days between two variants?

 

[pbaldy]

A Date variable can not accept a Null value. Variants are less efficient, but there shouldn't be a downside to using them. A Date/Time field in a table can be Null, but if memory serves, not ZLS.

Some believe that fields should never be Null, or there's a design problem. I agree to a point, but I think there are exceptions. For instance I've got a vehicle maintenance system that includes fields for the open and closing date/time of a vehicle being in the shop. Obviously, while it's there the close field is Null. To calculate the elapsed time, I simply replace the Null with Now() (the Nz() function is one way to do that).

 

[poporacer]

That is where I am...I have fields for dates of certain events. Some events might never happen and the others you input as they happen. I just ran into the problem where I need to save the data (that is now ZLS) into a date field...What to you suggest, Check the data to see if it is ZLS and skip if so, or convert to null and save? What would be the code to convert to null?
Here is what I think might work

if me.txtDARefer <> "" then rsExit![Date DA Referred] = Me.txtDARefer

 

[pbaldy]

Since events may or may not happen, a normalization argument could be made for another table, where the event would be a separate record if/when it occurred. I don't know your situation well enough to say either way, but I throw it out there.

Your proposed code should work fine to only populate the field if the value is appropriate.