Code help

[jln via AccessMonster.com]

Ok what im trying to do here is load 2 where statements. What i need it to do
as well is if CHF_CUTOFF_PERIOD is null i need it to show. but im getting an
error on what i have right now Type mismatch

Dim CurMonth As String
Dim CurMonth1 As String
Dim stDocName As String
Dim LastDayOfMonth As String
Dim strwhere As String
Dim strwhere1 As String
strwhere = ""

CurMonth = Cal
CurMonth1 = Month(CurMonth) - 1
'strwhere = strwhere & "[Investor_Number]=" & Me.txtInvNo
strwhere = strwhere & "[CHF_CUTOFF_PERIOD]=" & CurMonth1
strwhere1 = strwhere1 & "[Actual_Due_Date]=" & CurMonth

stDocName = "RPT_Daily_Cut_Off"
DoCmd.OpenReport stDocName, acViewPreview, , strwhere1 And strwhere

 

[George Nicholson]

1) If [Actual_Due_Date] is an actual date then you need to include date
delimiters:
strwhere1 = strwhere1 & "[Actual_Due_Date]= #" & CurMonth & "#"

2) And in any case:

DoCmd.OpenReport stDocName, acViewPreview, , strwhere1 & " And " &
strwhere

 

[jln via AccessMonster.com]

George

For some reason i keep getting a data type mismatch error.
Actual_Due_Date is a date in the table
CHF_CUTOFF_PERIOD is a text in table

1) If [Actual_Due_Date] is an actual date then you need to include date
delimiters:
strwhere1 = strwhere1 & "[Actual_Due_Date]= #" & CurMonth & "#"

2) And in any case:

DoCmd.OpenReport stDocName, acViewPreview, , strwhere1 & " And " &
strwhere

Ok what im trying to do here is load 2 where statements. What i need it to
do

[quoted text clipped - 18 lines]

stDocName = "RPT_Daily_Cut_Off"
DoCmd.OpenReport stDocName, acViewPreview, , strwhere1 And strwhere

 

[George Nicholson]

If [CHF_CUTOFF_PERIOD] is a text field then:

strwhere = strwhere & "[CHF_CUTOFF_PERIOD]= ' " & CurMonth1 & " ' "

Additional spaces have been added to the above for clarity, but need to be
removed. The final version should be:
strwhere = strwhere & "[CHF_CUTOFF_PERIOD]= '" & CurMonth1 & "'"

Your original line would have been fine if [CHF_CUTOFF_PERIOD] was a
numeric field.

--
HTH,
George

George

For some reason i keep getting a data type mismatch error.
Actual_Due_Date is a date in the table
CHF_CUTOFF_PERIOD is a text in table

1) If [Actual_Due_Date] is an actual date then you need to include date
delimiters:
strwhere1 = strwhere1 & "[Actual_Due_Date]= #" & CurMonth & "#"

2) And in any case:

DoCmd.OpenReport stDocName, acViewPreview, , strwhere1 & " And " &
strwhere

Ok what im trying to do here is load 2 where statements. What i need it
to
do

[quoted text clipped - 18 lines]

stDocName = "RPT_Daily_Cut_Off"
DoCmd.OpenReport stDocName, acViewPreview, , strwhere1 And
strwhere

 

[Klatuu]

I see a few problems here. First, I am suspisious of your data typing.
What is cal? Is it a calendar control? Does it return a date or a string?
CurrMonth1 is dimmed as a string, but you are assigning it a numeric value.
That works, but takes extra time for Access to coerse the type.
I see in you later post Actual_Due_Date is a date field, so that leads me to
believe CurrMonth should also be a date type.

And the biggest issue is you don't have an And between you criteria. Why
create two different strings?

'strwhere = strwhere & "[Investor_Number]=" & Me.txtInvNo
strwhere = strwhere & " AND [CHF_CUTOFF_PERIOD]=" & CurMonth1
strwhere = strwhere & "AND [Actual_Due_Date]=" & CurMonth

 

[jln via AccessMonster.com]

This is what is showing up in the locals window and im getting a you canceled
previous action.


: strwhere : "[CHF_CUTOFF_PERIOD]=2AND [Actual_Due_Date]=3/18/20080r
[CHF_CUTOFF_PERIOD]=" : String

I see a few problems here. First, I am suspisious of your data typing.
What is cal? Is it a calendar control? Does it return a date or a string?
CurrMonth1 is dimmed as a string, but you are assigning it a numeric value.
That works, but takes extra time for Access to coerse the type.
I see in you later post Actual_Due_Date is a date field, so that leads me to
believe CurrMonth should also be a date type.

And the biggest issue is you don't have an And between you criteria. Why
create two different strings?

'strwhere = strwhere & "[Investor_Number]=" & Me.txtInvNo
strwhere = strwhere & " AND [CHF_CUTOFF_PERIOD]=" & CurMonth1
strwhere = strwhere & "AND [Actual_Due_Date]=" & CurMonth

Ok what im trying to do here is load 2 where statements. What i need it to do
as well is if CHF_CUTOFF_PERIOD is null i need it to show. but im getting an

[quoted text clipped - 16 lines]

stDocName = "RPT_Daily_Cut_Off"
DoCmd.OpenReport stDocName, acViewPreview, , strwhere1 And strwhere

 

[jln via AccessMonster.com]

This is what is showing up in the locals window and im getting a you canceled
previous action.


: strwhere : "[CHF_CUTOFF_PERIOD]=2AND [Actual_Due_Date]=3/18/20080r
[CHF_CUTOFF_PERIOD]=" : String

I see a few problems here. First, I am suspisious of your data typing.
What is cal? Is it a calendar control? Does it return a date or a string?
CurrMonth1 is dimmed as a string, but you are assigning it a numeric value.
That works, but takes extra time for Access to coerse the type.
I see in you later post Actual_Due_Date is a date field, so that leads me to
believe CurrMonth should also be a date type.

And the biggest issue is you don't have an And between you criteria. Why
create two different strings?

'strwhere = strwhere & "[Investor_Number]=" & Me.txtInvNo
strwhere = strwhere & " AND [CHF_CUTOFF_PERIOD]=" & CurMonth1
strwhere = strwhere & "AND [Actual_Due_Date]=" & CurMonth

Ok what im trying to do here is load 2 where statements. What i need it to do
as well is if CHF_CUTOFF_PERIOD is null i need it to show. but im getting an

[quoted text clipped - 16 lines]

stDocName = "RPT_Daily_Cut_Off"
DoCmd.OpenReport stDocName, acViewPreview, , strwhere1 And strwhere

 

[jln via AccessMonster.com]

Klatuu and George

Thanks for the help Im getting close but i have one more thing

If CHF_CUTOFF_PERIOD is null and the Actual_Due_Date = Cal then i still need
it to show.

I see a few problems here. First, I am suspisious of your data typing.
What is cal? Is it a calendar control? Does it return a date or a string?
CurrMonth1 is dimmed as a string, but you are assigning it a numeric value.
That works, but takes extra time for Access to coerse the type.
I see in you later post Actual_Due_Date is a date field, so that leads me to
believe CurrMonth should also be a date type.

And the biggest issue is you don't have an And between you criteria. Why
create two different strings?

'strwhere = strwhere & "[Investor_Number]=" & Me.txtInvNo
strwhere = strwhere & " AND [CHF_CUTOFF_PERIOD]=" & CurMonth1
strwhere = strwhere & "AND [Actual_Due_Date]=" & CurMonth

Ok what im trying to do here is load 2 where statements. What i need it to do
as well is if CHF_CUTOFF_PERIOD is null i need it to show. but im getting an

[quoted text clipped - 16 lines]

stDocName = "RPT_Daily_Cut_Off"
DoCmd.OpenReport stDocName, acViewPreview, , strwhere1 And strwhere

 

[jln via AccessMonster.com]

Got it Thanks again

Klatuu and George

Thanks for the help Im getting close but i have one more thing

If CHF_CUTOFF_PERIOD is null and the Actual_Due_Date = Cal then i still need
it to show.

I see a few problems here. First, I am suspisious of your data typing.
What is cal? Is it a calendar control? Does it return a date or a string?

[quoted text clipped - 15 lines]