[AccessD] adhbCalendar returns INCORRECT value?! Extra info SOLVED

S D accessd667 at yahoo.com
Mon Feb 16 08:34:57 CST 2004


Gustav,
 
THANK YOU!!!!!
When copy-pasted the code and ran the code I got a very big smile on my ugly face!
 
WHY in G's name does this work?
 
Sander

Gustav Brock <gustav at cactus.dk> wrote:
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 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)

_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

---------------------------------
Do you Yahoo!?
Yahoo! Finance: Get your refund fast by filing online


More information about the AccessD mailing list