Solved Filter Report

[adamsro]

Hello,

I have a report i call to open but i would like to to only show items that are <= a numerical value that is entered before the form loads. Im trying to sort the days remaining field on the report by the value of the dtotext field. Ive tried various things, but nothing seems to work I either get nothing or all results or errors

The dtotext is the text field on the form the user enters a number default value is 360


Private Sub reportbtn_Click()

DoCmd.OpenReport "itemcheck", acViewReport, , "[days remaining] <= 'me.dtotext'"


End Sub

Thanks in advance for your help.

Rob

 

[plog]

1. You don't need quotes around values when comparing numbers.
2. When compiling strings, you concatenate (put together) literal strings and variables with the & sign:

So, the criteria should look like this:

"[days remaining] <= " & me.dtotext

3. You shouldn't use spaces in names, just makes coding and querying that much more difficult.

 

[DHookom]

Hello,

I have a report i call to open but i would like to to only show items that are <= a numerical value that is entered before the form loads. Im trying to sort the days remaining field on the report by the value of the dtotext field. Ive tried various things, but nothing seems to work I either get nothing or all results or errors

The dtotext is the text field on the form the user enters a number default value is 360


Private Sub reportbtn_Click()

DoCmd.OpenReport "itemcheck", acViewReport, , "[days remaining] <= 'me.dtotext'"


End Sub

Thanks in advance for your help.

Rob

The key is whether or not the [days remaining] field is numeric or short text. If numeric, plog has provided the solution. If it is text (not a good idea) then the code would be:

Private Sub reportbtn_Click()

  DoCmd.OpenReport "itemcheck", acViewReport, , "Val([days remaining]) <= " & Me.dtotext
 
End Sub

 

[adamsro]

days remaining is a calculated field based on 2 dates

 

[adamsro]

made the changes plog suggessted still doesnt sort, i get a pop up window asking for days remaining value

 

[DHookom]

The error message suggests there is no field in the report's record source named [days remaining]. It appears you might have calculated this value in the report and not the record source query. Can you confirm the calculation and where it exists?

 

[adamsro]

the calculated days remaining is from a SharePoint list. when i run the command without the sort i pull 4 items. all with the days remaining fields populated

 

[DHookom]

"SharePoint list" doesn't fully answer my question regarding the field being in the report's record source. Can you confirm you see the column in the report's record source and tell us if it is left or right aligned? Left aligned would suggest the value is text and right, numeric.

 

[adamsro]

The record source is a querie that has two tables one that holds the days remaining data type just shows as calculated. on the report property sheet is shows the days remaining as format general number.

Sorry not really sure if im looking at the right place you are speaking about but from report design view it is left aligned

 

[adamsro]

when i go to pick a control source in the report it is left aligned as well

 

[adamsro]

when i look at the report the days remaining are right aligned I think that is what you are asking. So yes, it is numerical value as it matches all the other numerical fields are right aligned text fields in the report are left aligned

 

[plog]

This is not a datatype issue, it cannot find [days remaining].

Step away from the report. Open the query the report is based on. Does it have a [days remaining] field in it? Is it spelled correctly? The VBA you have must spell it exactly the same way it is in the query.

The screenshot you posted has it spelled 'Days Remaning' (look carefully, it is short an 'i'). Is it that way in the query?

 

[adamsro]

yes query shows all numerical values in the days remaining field

 

[plog]

And there you go. See the misspelling?

 

[adamsro]

oh my it was a spelling error #$#!@

 

[adamsro]

it all works now thanks so much for your help