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.DebutP eriode)=#" & DebutDate & "#) AND ((tblCC.FinPeri ode)=#"
& FinDate & "#));"

DoCmd.RunSQL "DELETE tblCC.*, tblCC.DebutPeri ode, tblCC.FinPeriod e
FROM tblCC WHERE "(((tblCC.Debut Periode)=#" & DebutDate & "#) AND
((tblCC.FinPeri ode)=#" & FinDate & "#));"

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

thanxs

[downwitch]

Re: SQL Question

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 usqlDelocalizeQ ryDte(ByVal pvarDte As Variant, _
Optional pbleUsePoundSig nWrapper As Boolean
= True, _
Optional pbleReplaceNull WithToday As
Boolean = False) As String
On Error Resume Next

Dim strFormat As String

If IsNull(pvarDte) Or (pvarDte = #12:00:00 AM#) Then
If pbleReplaceNull WithToday = True Then
pvarDte = Date
strFormat = "m/d/yy"
Else
strFormat = ""
If pbleUsePoundSig nWrapper = True Then
usqlDelocalizeQ ryDte = "Null"
Else
usqlDelocalizeQ ryDte = "<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

usqlDelocalizeQ ryDte = Format(pvarDte, strFormat)

If pbleUsePoundSig nWrapper = True Then
usqlDelocalizeQ ryDte = "#" & usqlDelocalizeQ ryDte & "#"
End If

End Function