Due Date

[WhiskyLima]

Hi All, I have a continuous form which has a number of entries in it and one of the fields is a due date for a payment. I have the whole form based on a query with a few combo box controls to 'filter' the data. Each combo box relates to a criteria in the query. All working and does what it should, nothing complicated there. What im now trying to do is to add another criteria for the 'dueDate' field which will filter the info depending on the selection in the due date combo box 'dueDateSelect'. Here is what I have attempted to use:

IIf([forms]![All Invoices]![dueDateSelect]="All Invoices","*", IIF([forms]![All Invoices]![dueDateSelect] = "Due This Week", >= Date() AND <= DateAdd("d", 7, Date()), < Date()))

This doesn't work at all and produces and invalid argument error. I know it's probably totally wrong but I wonder if someone could help me find the correct criteria for this 'dueDate' Field in my query.

There are three criteria for this one "All Invoices" to show everything, "Due This Week" to show everything between now and seven days time and "Overdue" To show everything that has not been paid and has a due date in the past.

Many Thanks

 

[CJ_London]

presumably you are trying in incorporate this into a criteria or filter?

If so, please provide the context in which it is being used - is it in a query, are you using it in VBA to build a string or what?

Simplistically if in a query, you cannot build a criteria in this way and if in VBA you need to pass out the result from the iif statement as a string. Without knowing the context, we cannot suggest the right solution

 

[WhiskyLima]

Sorry, yes it is in the criteria of the query, sorry I thought I made that clear, I have just had another look over this and realise that what im trying to do is not going to work as the criteria for the query. Is there another way to filter the continuous form which already has some criteria in the filter?

Thanks for your swift responce

 

[namliam]

Are you using that IIF in your where clause of your query???

Using IIF in where clauses is IFFY at best (yes pun intended)

Same goes for things like "*", which has its own issues...

Instead I will advice you to "fix" your working code so it is more flexible...
Instead of inbedding all possible options into one query.... I strongly advice you to write the SQL on demand in VBA.

If Me.dueDateSelect="All Invoices" Then
    'do nothing to the sql
elseif  Me.dueDateSelect = "Due This Week" then
    mywhere = mywhere & " AND YourDueDateField between Date() AND <= DateAdd("d", 7, Date())
else Me.dueDateSelect = "Overdue" then
    myWhere = mywhere & " AND Yourduedatefield < Date() )
endif

Obviously expand this for your other fields, This gives you a lot more power and flexibity vs trying to use IIFs to work around all the possible issues you will encounter

 

[WhiskyLima]

Yeah I was afraid that would be the case, didn't want to go down this route due to laziness and the four other 'filters' im using in the same way. I suppose I will crack on with that method and re-write the rest of it in this way - will be better off in the long run.

Cheers All!

 

[WhiskyLima]

Just another quick one, how do I query the continuous form from the VBA SQL?

 

[namliam]

it is possible using IIF ....

Between date1 and date2

Date1 and date two would each have to be replaced by your (complicated) IIF to show the right data...Dynamic SQL though so much more powerfull AND will be faster in executing. Since regardless of use or not Each IIF will be executed...
On top of which proper SQL can use indexes on your table(s), where as this IIF will not use them.

 

[namliam]

Just another quick one, how do I query the continuous form from the VBA SQL?

You feed the continues form the sql .... Either by writing the SQL into the form's rowsource or by feeding the sql to an SQL object.

Query object:
Currentdb.Querydefs("YourQuery").SQL = "select bla bla..."

Form rowsource (a little cleaner), though I am not 100% on this aircode, but it should look something like:
Me.Forms("YourSubform").Rowsource = "Select bla bla"

 

[WhiskyLima]

Ah Cool, Ok Thanks! I'll give that a go!

 

[CJ_London]

Me.Forms("YourSubform").Rowsource = "Select bla bla"

Assuming your code is in the main form it would be

mysubformname.form.recordsource="SELECT bla bla"


or you can use the filter property

mysubformname.form.filter= myCriteriaStr
mysubformname.form.filteron=true

Also, if you are using the linkchild and linkmaster properties of the subform, you may need to reinstate them if you change the recordsource - you'll need to check.

Or you can incorporate the linking into your recordsource criteria

 

[WhiskyLima]

Thank CJ, just working on it now so I will let you know which way I go soon

 

[WhiskyLima]

Hey guys, my brain isn't working at all today. I should probably have made clear it's not a sub form, it's the main form, does that make any difference? Heres what I have tried:

clientFilter = "WhiskyLima"
 strSQL = "SELECT * FROM [Invoices] WHERE "
 If Not IsNull(Me.clientFilter) Then
    strSQL = strSQL & " [ClientID] Like " & "*" & [Forms]![All Invoices]![clientFilter] & "*"
End If
 MsgBox (strSQL)
 Forms![All Invoices].Form.Filter = strSQL
Forms![All Invoices].FilterOn = True

Kept it basic and simple to start with but if I can get this working im sure I can crack the rest. So where am I going wrong here?

Thanks a lot for the help guys!

 

[namliam]

sql goes in the rowsource.... the full SQL you just build
The where clause goes into the filter.... Only this part >> " [ClientID] Like " & "*" & [Forms]![All Invoices]![clientFilter] & "*"

You are mixing the two different things