Gustav Brock
gustav at cactus.dk
Mon Feb 16 04:00:55 CST 2004
Hi Sander
I would check two things.
1. The input looks like coming from a textbox. If a Debug.Print shows
wrongly translated dates, try:
Call CalculateDays(CDate(txtStartDatum), CDate(txtEindDatum), arrDates())
2. Date expression in SQL must be US formatted according to the Lembit
method:
strWhere =
"WHERE a.date = #" & Format(dtmCurrDate, "mm\/dd\/yyyy") & "# "
/gustav
> I'm creating a sql string to fill a table.
> The (source) table I'm using has a date field with no formatting. I'm linking this table and I'm NOT allowed to change it.
> The date from the adhbCalendar is used to determine wich date I must use for the WHERE statement.
> The destination table has also a datefield without formatting.
> I use this code to show the date on screen:
> txtEindDatum = adhDoCalendar(txtEindDatum)
> This date is put inside an array (date) like this:
> Dim arrDates() As Date
> '-----------------------------------------------
> 'Step 1: Fill Date Array
> '-----------------------------------------------
> Call CalculateDays(txtStartDatum, txtEindDatum, arrDates())
> Function CalculateDays(dtmStartDatum As Date, dtmEindDatum As Date, ByRef arrDates() As Date) As Integer
> Dim intCounter As Integer
> On Error GoTo CalculateDays_Error
> intCounter = 0
> Do While (DateDiff("d", dtmStartDatum, dtmEindDatum) >= 0)
> ReDim Preserve arrDates(intCounter)
> arrDates(intCounter) = dtmStartDatum
> If DateDiff("d", dtmStartDatum, dtmEindDatum) > 0 Then
> intCounter = intCounter + 1
> dtmStartDatum = dtmStartDatum + 1
> Else
> Exit Do
> End If
> Loop
> 'intCountDate = UBound(arrDates)
> CalculateDays = UBound(arrDates)
> 'CalculateDays = intCountDate
> CalculateDays_Exit:
> ' Collect your garbage here
> Exit Function
> CalculateDays_Error:
> ' Collect your garbage here
> MsgBox "An error occured!" & vbCrLf & _
> "Error number : " & Err.Number & vbCrLf & _
> "Error description: " & Err.Description & vbCrLf & _
> "Error source : " & Err.Source
> GoTo CalculateDays_Exit
> End Function
> Then I build the select statement:
> For intCountDate = 0 To UBound(arrDates)
> 'dtmCurrDate = Format(arrDates(intCountDate), "mm/dd/yyyy")
> dtmCurrDate = arrDates(intCountDate)
> .....
> .....
> If IsWeekend(dtmCurrDate) Then
> 'check if
> 'Call MsgBox("This day (" & dtmCurrDate & ") is either a saturday or a sunday", vbInformation, "Weekend")
> strWhere = "WHERE a.date = #" & dtmCurrDate & "# "
> ...
> ....
> Function IsWeekend(dtmDate As Date) As Boolean
> On Error GoTo IsWeekend_Error
> 'Check to See if Weekend
> If (Weekday(dtmDate) = vbSaturday) Or (Weekday(dtmDate) = vbSunday) Then
> IsWeekend = True
> Else
> IsWeekend = False
> End If
> IsWeekend_Exit:
> ' Collect your garbage here
> Exit Function
> IsWeekend_Error:
> ' Collect your garbage here
> MsgBox "An error occured!" & vbCrLf & _
> "Error number : " & Err.Number & vbCrLf & _
> "Error description: " & Err.Description & vbCrLf & _
> "Error source : " & Err.Source
> GoTo IsWeekend_Exit
> End Function
> I don't see anything funny, anybody else?
> SD
> Gustav Brock <gustav at cactus.dk> wrote:
> Hi Sander
> It sounds like you have become a victim of non-internationalized code.
> But it is hard to tell with no code and no info on how you are using
> it.
> /gustav
>> I'm going nuts! I'm using the calendar form from the ADHB 2000 and it returns the WRONG value!?
>> When I select januari 4th 2004 (wich is a sunday) it returns April 1st 2004!! How is this possible?
>> The function below has the following values (after selecting januari 4th 2004):
>> Year = 2004
>> Month = 1
>> Day = 4
>> Public Property Get Value() As Date
>> Value = DateSerial( _
>> Me.Year, Me.Month, Me.Day)
>> End Property
>> i'm using
>> A2k english
>> Win2000 (reg settings => english; date = dd/mm/yyyy)