Problem with stLinkCriteria

[Ray]

Am having problems with the syntax of the Time section of
the following code. Can you help

If Not IsNull(Me![StartDate]) Then
vStr = "[Date]>" & Me![StartDate] & "#"
End If

If Not IsNull(Me![EndDate]) Then
If Not IsNull(vStr) Then vStr = vStr & " And "
vStr = vStr & "[Date]<" & Me![EndDate] & "#"
End If

'........Time..............................

If Not IsNull(Me![StartTime]) Then
If Not IsNull(vStr) Then vStr = vStr & " And "
vStr = vStr & "[Time]>#" & "#" & Me![StartTime] & "#"
End If
If Not IsNull(Me![EndTime]) Then
If Not IsNull(vStr) Then vStr = vStr & " And "
vStr = vStr & "[Time]<#" & "#" & Me![EndTime] & "#"
End If

stLinkCriteria = vStr
DoCmd.OpenForm stDocName, , , stLinkCriteria

 

[Wayne Morgan]

Just as you have to put quotes on each end of a string, you need the # signs
on each end of the date and/or time.

If Not IsNull(Me![StartDate]) Then
vStr = "[Date]>" & Me![StartDate] & "#"
End If

vStr = "[Date]>#" & Me![StartDate] & "#"

If Not IsNull(Me![EndDate]) Then
If Not IsNull(vStr) Then vStr = vStr & " And "
vStr = vStr & "[Date]<" & Me![EndDate] & "#"
End If

vStr = vStr & "[Date]<#" & Me![EndDate] & "#"

If Not IsNull(Me![StartTime]) Then
If Not IsNull(vStr) Then vStr = vStr & " And "
vStr = vStr & "[Time]>#" & "#" & Me![StartTime] & "#"
End If

vStr = vStr & "[Time]>#" & Me![StartTime] & "#"

If Not IsNull(Me![EndTime]) Then
If Not IsNull(vStr) Then vStr = vStr & " And "
vStr = vStr & "[Time]<#" & "#" & Me![EndTime] & "#"
End If

vStr = vStr & "[Time]<#" Me![EndTime] & "#"

This is assuming that you are doing Date OR Time, but not both. If you are
doing both, the concatenation will need to be different. You will need to
concatenate in each time with its date and put a # sign on each end of the
entire date/time.

Also, it appears that you have fields named "Date" and "Time". These are
reserved words and can cause you problems when used as field or object
names.

 

[Ray]

You are right, I am doing both. I see the error in my
logic. If I make one field (not 2) in the target table
and format it as general date how do I concatinate the
StartDate and StartTime to make it match? Much more
confusing than text.

-----Original Message-----
Just as you have to put quotes on each end of a string, you need the # signs
on each end of the date and/or time.

If Not IsNull(Me![StartDate]) Then
vStr = "[Date]>" & Me![StartDate] & "#"
End If

vStr = "[Date]>#" & Me![StartDate] & "#"

If Not IsNull(Me![EndDate]) Then
If Not IsNull(vStr) Then vStr = vStr & " And "
vStr = vStr & "[Date]<" & Me![EndDate] & "#"
End If

vStr = vStr & "[Date]<#" & Me![EndDate] & "#"

If Not IsNull(Me![StartTime]) Then
If Not IsNull(vStr) Then vStr = vStr & " And "
vStr = vStr & "[Time]>#" & "#" & Me![StartTime] & "#"
End If

vStr = vStr & "[Time]>#" & Me![StartTime] & "#"

If Not IsNull(Me![EndTime]) Then
If Not IsNull(vStr) Then vStr = vStr & " And "
vStr = vStr & "[Time]<#" & "#" & Me![EndTime] & "#"
End If

vStr = vStr & "[Time]<#" Me![EndTime] & "#"

This is assuming that you are doing Date OR Time, but not both. If you are
doing both, the concatenation will need to be different. You will need to
concatenate in each time with its date and put a # sign on each end of the
entire date/time.

Also, it appears that you have fields named "Date" and "Time". These are
reserved words and can cause you problems when used as field or object
names.

--
Wayne Morgan
MS Access MVP


.

 

[Wayne Morgan]

Separately,
#3/14/2005#
#3:00 PM#

Combined,
#3/14/2005 3:00 PM#

Here is an example doing this with the Start date and time. This assumes
that the time will be null if the date is null. If not, then it will require
a little modification.

If Not IsNull(Me![StartDate]) Then
If Not IsNull(Me![StartTime]) Then
vStr = "[Date]>" & Me![StartDate] & " " & Me![Start Time] & "#"
Else
vStr = "[Date]>" & Me![StartDate] & "#"
End If
End If

 

[Ray]

Have done what was suggested (at least I think so). The
target table was changed to have only one field named
UpdateDate(general date) I am trying to concatinate the
two textboxes (one is short date and the other is long
time) to use as selection criteria for the UpdateDate
field. Have come up with below but still get syntax
error. Please help.

If Not IsNull(Me![StartDate]) And Not IsNull(Me!
[StartTime])
Then vStr = "[UpdateDate]>##" & Me![StartDate] & "##" &
Me![StartTime] & "##"
End If

If Not IsNull(Me![EndDate]) And Not IsNull(Me![EndTime])
Then If Not IsNull(vStr) Then vStr = vStr & " And "
vStr = vStr & "[UpdateDate]<=##" & Me![EndDate] & "##" &
Me![EndTime] & "##"
End If

 

[Ray]

That worked fine. Noticed you left out a # after the >.
But was able to figure out. Excellent help. Thankyou very
much

-----Original Message-----
Separately,
#3/14/2005#
#3:00 PM#

Combined,
#3/14/2005 3:00 PM#

Here is an example doing this with the Start date and time. This assumes
that the time will be null if the date is null. If not, then it will require
a little modification.

If Not IsNull(Me![StartDate]) Then
If Not IsNull(Me![StartTime]) Then
vStr = "[Date]>" & Me![StartDate] & " " & Me! [Start Time] & "#"
Else
vStr = "[Date]>" & Me![StartDate] & "#"
End If
End If

--
Wayne Morgan
MS Access MVP

You are right, I am doing both. I see the error in my
logic. If I make one field (not 2) in the target table
and format it as general date how do I concatinate the
StartDate and StartTime to make it match? Much more
confusing than text.

.