DAO database Date in string error...

[ItIsHardToProgram]

I am using a code that works on one of my form in another form.

1) I have changed variables

2) I made sure there is no conflict

3) There are no empty vars, and even if there were it should not do errors.

Here is the code I use with the highlight part were it messes up

Private Sub SelSemDep_AfterUpdate()
      ' Rechercher l'enregistrement correspondant au contrôle.
    
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strWhereDep As String
    
    TestWhileDep = False
  
  startdate = Me.SelSemDep.Column(1)
  enddate = Me.SelSemDep.Column(2)


Set db = CurrentDb()
Set rs = db.OpenRecordset("Dépense", dbOpenDynaset)
[highlight]strWhereDep = "[Date1] = " & "#" & Str(Nz(startdate)) & "#"[/highlight]
rs.FindFirst strWhereDep
If rs.NoMatch Then
TestWhileDep = True
End If
If TestWhileDep = True Then
AddNewRecord
'rs![date1] = Str(Nz(startdate))
'rs![date2] = Str(Nz(enddate))
'rs![Nom] = Y
'rs![PreN] = z
'rs![no_emp] = EmpNumber()
'rs.Update
End If
Me.Bookmark = rs.Bookmark
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing

If TestWhileDep = False Then
Set db = CurrentDb()
Set rs = db.OpenRecordset("Dépense", dbOpenDynaset)
strWhereDep = "[Date1] = " & "#" & Str(Nz(startdate)) & "#"
rs.FindFirst strWhereDep
Me.Bookmark = rs.Bookmark
End If
End Sub

SelSemDep is a combobox that contains Date values.
StartDate is = to 2006-01-29, for example.
Date1 is the field name in the table Dépense.

The code gives me the following error: Error 13, incompatibity between the 2 statements.

The problem I am having is that strWhere is a string, and I do not understand why a string could have incompatibility between 2 statements that are obviously strings..... it is not overflown or anything...

Even more troubling, that statements is used at alot of places in my database and works fine...

Please enlighten me... any suggestion will probably help, and an explanation would be the greatest, thank you.

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[easyit]

...what is the result of :

Str(Nz(startdate))

EasyIT

"Do you think that’s air you're breathing?

 

[ItIsHardToProgram]

The only problem I might be seeing, which might be it, is that im directly referencing to a table and not to a query, could that be the cause of my problem?

If that is my problem, could I in any way reference to a table using the code I have set, or is it strictly impossible...

Thank you...

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[ItIsHardToProgram]

str turns startdate in a string, and nz eliminates null, if there are any, so it is = to "2006-01-29" if that is the date, I am prety sure this is not my problem....

I have tried referencing to a querry, still get the same error and querry is working fine, Error 13 is a runtime error, Type mismatch...

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[ItIsHardToProgram]

Nevermind, you are right, str(nz(startdate)) does not return a valid result, but Ive used this combination in another form...... what would you suggest?

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[easyit]

"pretty sure..."

"Knowing that you know is the greatest sign of stupidity...

Type mismatch...

better stop being pretty & start being sure...



EasyIT

"Do you think that’s air you're breathing?

 

[ItIsHardToProgram]

you are right, but that does not fix my problem... Why wouldnt access turn a date in a string, especialy since it did it for me in another part of this project...

Do you have a solution?

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[ItIsHardToProgram]

I found a solution, but I doubt it is the best way to handle this.

I have changed startdate to a string and it works, but I believe that handling a date as a string in a code might bring some errors.... what do you think?

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[easyit]

It is a bit difficult to debug your code, but since there is a type mismatch on the given line I would start by replacing the variables for a hard value, for testing purposes. Next I would comment out the line, to see if the error is actually on this line, or maybe generated elsewhere. Use debug.print to see what's in the variables...

Usually a null vlaue being entered into a string gives thsi kind of erorr. R U sure me.SelSemDep.Column(1) isn't null?


EasyIT

"Do you think that’s air you're breathing?

 

[ItIsHardToProgram]

I am 100% sure it is not null, this proven by the fact that replacing startdate from date to string works in the code... the error is really generated because it can't and will not transform a date in a string. with a hard value, it works...

Why would it mismatch here and not elsewhere, I am really lost.

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[SkipVought]

Convert a DATE to a STRING...

strDate = Format([RealDate],"yyyy/mm/dd")

Skip,

_[red][/red]

 

[ItIsHardToProgram]

Thank you very much, but why did it work earlyer in my code??? in another form... the current code works:

Private Sub SelSem_AfterUpdate()
      ' Rechercher l'enregistrement correspondant au contrôle.
    
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strWhere As String
    
    TestWhile = False
  
  startdate = Me.SelSem.Column(1)
  enddate = Me.SelSem.Column(2)


Set db = CurrentDb()
Set rs = db.OpenRecordset("Ftemps", dbOpenDynaset)
strWhere = "[Date1] = " & "#" & Str(Nz(startdate)) & "#"
rs.FindFirst strWhere
If rs.NoMatch Then
TestWhile = True
End If
If TestWhile = True Then
AddNewRecord
'rs![date1] = Str(Nz(startdate))
'rs![date2] = Str(Nz(enddate))
'rs![Nom] = Y
'rs![PreN] = z
'rs![no_emp] = EmpNumber()
'rs.Update
End If
Me.Bookmark = rs.Bookmark
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing

If TestWhile = False Then
Set db = CurrentDb()
Set rs = db.OpenRecordset("Ftemps", dbOpenDynaset)
strWhere = "[Date1] = " & "#" & Str(Nz(startdate)) & "#"
rs.FindFirst strWhere
Me.Bookmark = rs.Bookmark
End If



End Sub

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.