Access Date format vs. VBA module

[RinaGreen]

Hello,

I am confused with the difference in Access and VBA syntax


I know that in Access it would be:
dMyDate between 10/1/2001 AND 12/10/2002

However it generate a compile error in VBA module which I never used before.

Could you please help me with that?

THANKS A LOT!

Rina

 

[grande]

dMyDate between #10/1/2001# AND #12/10/2002#

-------------------------
Just call me Captain Awesome.

 

[RinaGreen]

. I added # ...but It generate Compile and Syntax error anyway:-(

 

[SkipVought]

Please post ALL your code.

Skip,
[sub]
[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?" [/sub]

 

[Bugget]

I think it is because BETWEEN is SQL and VBA uses comparison operators. Try:

IF dMyDate > 10/1/2001 AND dMyDate < 12/10/2002 Then
...
END IF

 

[RinaGreen]

Sub deleteDupl()
Dim cmd As ADODB.Command
Dim rst As Recordset
Dim strSQL As String
Dim mID As String
Dim dShot As Date
Dim mAccept As String
Dim mAsked As String

Set cmd = New ADODB.Command
Set rst = New ADODB.Recordset

'fill a recordset with distinct member ID's
cmd.ActiveConnection = CurrentProject.Connection
'strSQL = "SELECT DISTINCT [MEMBER ID] FROM orig_"
'cmd.CommandText = strSQL
Set rst = cmd.Execute
'delete cycle

If Not rst.EOF Then rst.MoveFirst
Do While Not (orig_)

mID = orig_.[MEMBER ID]
mAccept = orig_.[ACCEPTS]
mAsked = orig_.[Asked]
dShot = orig_.[Med]

Do While orig_.[MEMBER ID] = mID
Select Case mID
Case 1
dShot between #10/1/2001# AND #12/10/2002# AND bAccept = "y" And mAsked = "YES"

rst.MoveNext
Case 2

dShot between #10/1/2001# AND #12/10/2002# AND bAccept="y"

rst.MoveNext
Case 3
dShot between #10/1/2001# AND #12/10/2002# AND mAsked ="YES"

rst.MoveNext
Case 4
dShot between #10/1/2001# AND #12/10/2002#
rst.MoveNext
Case 5
bAccept = "yes" And mAsked = "YES"

rst.MoveNext
Case 6
bAccept = "y"

rst.MoveNext

Case 7
mAsked = "YES"

rst.MoveNext

Case Else

rst.Delete
End Select

Loop
Loop


Set rst = Nothing
Set cmd = Nothing

End Sub

 

[Sh0jin]

Dim dMyDate As Date
Dim dDateMin As Date
Dim dDateMax As Date

'Note dMin, dMax, date are inputted from form text boxes
'but same concept should apply

dDateMin = CDate(dMin) 'dMin = 10/12/2001
dDateMax = CDate(dMax) 'dMax = 12/10/2002
dMyDate = CDate(date)

If dMyDate >= dDateMin And dMyDate <= dDateMax Then
Text3.Value = "True"
Else
Text3.Value = "False"
End If

Hope this helps
Sh0jin

 

[traingamer]

Select Case mID
Case 1 ' this only runs if mId = 1
Case 2 ' this only runs if mId = 2

I think you want to use nested if statements lile:

If dShot between #10/1/2001# AND #12/10/2002# AND bAccept = "y" And mAsked = "YES" Then
 rst.MoveNext 
else if  dShot between #10/1/2001# AND #12/10/2002#  AND bAccept="y" then etc.

among other changes

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill