date in vba ?? (1 Viewer)

[swarv]

Just a quick one:

I have this now:

Code:

Text2.SetFocus
Dim txtdept As String
Dim txtdept2 As String
txtdept = Text2.Text
txtdept2 = "dept='" & txtdept & "'"
dt0 = DTPicker0.Value
fromdate = "'CreatedDate > #" & DTPicker0 & "#'"
todate = "'CreatedDate < #" & DTPicker1 & "#'"
strr = txtdept2 & " AND 'CreatedDate BETWEEN #" & DTPicker0 & "# AND #" & DTPicker1 & "#'"
DoCmd.OpenReport "Copy of inventory transactions - Choose Dept", acViewPreview, , strr

It runs fine but shows all the dates on the report, not just the ones in the range. CreatedDate is a field on the report and is in shortdate format.

What would be the reason for this?

thanks
Martin

 

[VilaRestal]

You've put the CreatedDate Between clause in single quotes. I guess that gets converted to True.

It should be

strr = txtdept2 & " AND CreatedDate BETWEEN #" & DTPicker0 & "# AND #" & DTPicker1 & "#"

By the way, is there a reason you're setting focus to Text2 and using its Text property?

I expect you don't need to and the code could be rewritten thus:

Dim strr As String
strr = "dept='" & Text2 & "' AND CreatedDate BETWEEN #" & DTPicker0 & "# AND #" & DTPicker1 & "#"
DoCmd.OpenReport "Copy of inventory transactions - Choose Dept", acViewPreview, , strr

 

[swarv]

Hi,
Thanks for this. But it is now asking me for the changeddate in an input box as a parameter. This is what was happening before.

Your code does make it a lot simpler though.
Any ideas?
thanks
Martin

 

[VilaRestal]

changeddate? Do you mean CreatedDate? (changeddate isn't mentioned here)

Is that a field in your table? If not then that's why it's asking for it.

Otherwise, do you get it asking for parameter when you open the report manually (without this filter)?

 

[swarv]

sorry I meant createddate
its not a field in the table. created date is a field but on the report its called createddate.
if I take out the vba it works fine, just shows all records. Also works if I just put in the dept= bit of the code.

thanks

 

[VilaRestal]

Sorry, I meant is it a field in the recordsource of the report.

Perhaps it should be [Created Date] instead

 

[swarv]

I have attached a screenshot - does this help?
Hooefully that will answer your question?
I just tried witht he []'s but still no luck - still asks.

 

[VilaRestal]

OK, [Created Date] needs to be 'defined' in the report's recordsource. I can't tell whether it is. I'm guessing it will be a field in a table that forms part of the recordsource.

Once it is then the following should work:

Dim strr As String
strr = "dept='" & Text2 & "' AND [Created Date] BETWEEN #" & DTPicker0 & "# AND #" & DTPicker1 & "#"
DoCmd.OpenReport "Copy of inventory transactions - Choose Dept", acViewPreview, , strr

If it asks for the value of [Created Date] then that means there is no field called [Created Date] in the report's recordsource and it needs to be included there. I can't tell you how without knowing the tables and queries behind the report.

 

[swarv]

that works now - looks like I was using createddate instead of created date.
thanks for your help with this.

 

[Pat Hartman]

Text2.SetFocus
txtdept = Text2.Text

The .text property is only available when a control has the focus. That's why you needed to set focus to the control before referencing it. Once a control looses the focus, its .text property and .value property will always be the same. The only time they may be different is when the control has the focus and the user is actively typing in it. The .value property is the default property and so conventionally it is omitted (it must be omitted when referencing form fields from queries).

txtdept = Me.Text2

looks like I was using createddate instead of created date

There's no time like the present to get rid of all the embedded spaces