Problem with date field...Please Help

[Rameez]

Sub testdate(t1 As Date)
Dim s As Integer
t1 = Format(t1, "mm/dd/yy")
Debug.Print t
s = DatePart("m", t1)
Debug.Print s
End Sub

Call testdate(06/19/2009)
is giving 12
Call testdate(#06/19/2009#)
is giving 06....(correct value)

In my code, I have to extract the month and year part from cs4
Function IsDupeslam(cs1 As String, cs2 As String, cs3 As String, cs4 as date)
Dim y As Integer
y = DatePart("m", cs4)
debug.print y
End Function
obiviously the above code gives wrong result....
The solution i think will be to put the leading and trailing # to the date field...

Function dater11(d1 As Date)
Dim m1 As Integer
Debug.Print d1
m1 = Month(" & # d1 # &")...this code is to be corrected....
Debug.Print m1
End Function


Please help

 

[Poppa Smurf]

Change y = DatePart("m", cs4) to y = month(cs4)

for Year y = Year(cs4).

Note it is advisable to use 4 digits for year "YYYY" as in 2009

 

[Rameez]

allan, thanks for replying....

Function IsDupeslam(cs1 As String, cs2 As String, cs3 As String, cs4 As Date)
Dim y As Integer
y = Month(cs4)
Debug.Print y
End Function

Call Isdupeslam("aa","bb","cc",1/1/2009)

gives 12...??!!!

Call Isdupeslam("aa","bb","cc",#1/1/2009#)
gives 1....which is correct.

the problem is i cannot hardcode this date....

 

[Poppa Smurf]

Function IsDupeslam(cs1 As String, cs2 As String, cs3 As String, cs4 As Date)
Dim y As Integer
y = Month(cs4)
Debug.Print y
End Function

Call Isdupeslam("aa","bb","cc",1/1/2009)

gives 12...??!!!

Call Isdupeslam("aa","bb","cc",#1/1/2009#)
gives 1....which is correct.

the problem is i cannot hardcode this date....

First remove the Debug.Print y I used it for testing.

I know you cannot hard code the date. But in your code you have Function IsDupeslam(cs1 As String, cs2 As String, cs3 As String, cs4 As Date) this implies that you are calling this function from a form etc. and passing the parameters cs1, cs2, cs3 and cs4 to the function.

What is the purpose of the function?

 

[Rameez]

here is the body of my function
sub updatedb()
....
....
set rs.openrecordset(.....)
rs.movefirst
do until rs.eof
dim flag1 as boolean
dim temp1 as string
dim temp2 as string
dim temp3 as string
dim temp4 as date
temp1=rs!field1
temp2=rs!field2
temp3=rs!field3
temp4=rs!field4 'date field

flag1= isdupeslam(temp1,temp2,temp3,temp4)
....
....

rs.movenext
loop
....
....

the problem is with the date field...temp4...dates are not getting passed

 

[Rameez]

First remove the Debug.Print y I used it for testing.

I know you cannot hard code the date. But in your code you have Function IsDupeslam(cs1 As String, cs2 As String, cs3 As String, cs4 As Date) this implies that you are calling this function from a form etc. and passing the parameters cs1, cs2, cs3 and cs4 to the function.

What is the purpose of the function?

I have wriiten a query based on one table t1...I run through that query and search for any records with same data in about 4 fields...if they match then proceed...

 

[Poppa Smurf]

Just quickly I would check that the field4 as in temp4=rs!field4 'date field is actually a date format in the table and not a text field.

 

[Rameez]

it is a date field....100%.... type used is medium date

 

[Rameez]

I think the prob is with those # marks...without those hash marks...if i hardcode the date, i get a wrong answer....i have to some how attach # to the date value which comes each time...

like temp4="#" & rs!field4 "#"

but i don't know how exaclty...the above when doesn't work

 

[Poppa Smurf]

Have you got date values in your table and query?

Make a copy of your table call it master or some other name. In the original delete all records except 1, we will use this for testing.

Put the DIM statements the next line after sub updatedb()
Dim statements go at the top of the code.

After temp4=rs!field4 'date field put the following code msgbox("temp4: " & temp4) this will display the current value of temp4. If a date is displayed then you know that you are passing a date to the function.

In the function after Dim y As Integer put the following code msgbox("cs4: " & cs4). Likewise if you have a date value then the date has been passed to the function.

If all else fails please send a zipped copy with some test data and I will look at it for you.

 

[WIS]

Sub testdate(t1 As Date)
Dim s As Integer
t1 = Format(t1, "mm/dd/yy")Debug.Print t
s = DatePart("m", t1)
Debug.Print s
End Sub

Call testdate(06/19/2009)
is giving 12
Call testdate(#06/19/2009#)
is giving 06....(correct value)

Problem:

You have Sub testdate(t1 As Date), but then you have t1 = Format(t1, "mm/dd/yy"). t1 has changed from a date to a string - that's why you need the # around it.