SQL Question

[Sven Daems]

Hy

In a table caled tblcc, I've a great numbre of records. The table
contains 2 field DebutPeriode & FinPeriode. A part of the records
(about 800) have as DebutPeriode 01/09/2004 & as FinPeriode
31/12/2004, the others (about 400) have the other value (01/01/2005 as
DebutPeriode & 28/02/2005 as FinPeriode). The thing is, I want to move
the records of one periode to another table, called tblArchCC.

I've made a form with 2 listbox's: one with 2 values ("De 01/09/2004 à
31/12/2004" & "De 01/01/2005 à 28/02/2005") called lstNonArch, another
called lstArch witch is empty and a button. When the user pushes the
button, the Periode is moved to another table (from tblPeriode to
tblArchPeriodes), this part is working fine. I've just made a SQL
query, and it run's from VB. After the Periode, the records should be
moved to the other table. Therefor I've made the same solution, only
this SQLquery doesn't work very good. When I try to move the periode
"De 01/01/2005 à 28/02/2005", it's all going well, but when I try it
whit the other periode ("De 01/09/2004 à 31/12/2004") it only moves
the periode but not the records.

The date's are stored in a variable, type date caled DebutDate &
FinDate
The first query copies the values into the table tblArchCC, the second
deletes the values from the table tblCC.

DoCmd.RunSQL "INSERT INTO tblArchCC SELECT tblCC.* FROM tblCC WHERE
(((tblCC.DebutPeriode)=#" & DebutDate & "#) AND ((tblCC.FinPeriode)=#"
& FinDate & "#));"

DoCmd.RunSQL "DELETE tblCC.*, tblCC.DebutPeriode, tblCC.FinPeriode
FROM tblCC WHERE "(((tblCC.DebutPeriode)=#" & DebutDate & "#) AND
((tblCC.FinPeriode)=#" & FinDate & "#));"

Does anybody knows why the query's only works with one periode & not
with the other?

thanxs

[downwitch]

Try using an strSQL string and debug-reading the SQL string before you
process it. I suspect that it's because you're using French date
format (dd/mm/yy), while SQL expects American dating (mm/dd/yy). The
below function fixes this, and will even add the pound signs around it
if you like
--------
Public Function usqlDelocalizeQryDte(ByVal pvarDte As Variant, _
Optional pbleUsePoundSignWrapper As Boolean
= True, _
Optional pbleReplaceNullWithToday As
Boolean = False) As String
On Error Resume Next

Dim strFormat As String

If IsNull(pvarDte) Or (pvarDte = #12:00:00 AM#) Then
If pbleReplaceNullWithToday = True Then
pvarDte = Date
strFormat = "m/d/yy"
Else
strFormat = ""
If pbleUsePoundSignWrapper = True Then
usqlDelocalizeQryDte = "Null"
Else
usqlDelocalizeQryDte = "<invalid date value>"
End If
Exit Function
End If
Else
If uSplitDateTime("Time", pvarDte) > #12:00:00 AM# Then
strFormat = "m/d/yy hh:nn:ss"
Else
strFormat = "m/d/yy"
End If
End If

usqlDelocalizeQryDte = Format(pvarDte, strFormat)

If pbleUsePoundSignWrapper = True Then
usqlDelocalizeQryDte = "#" & usqlDelocalizeQryDte & "#"
End If

End Function