Printable View
can I put an if statement in the where clause?
I have a form the shows the users transactions, I have two combo boxes, one for year, and one for month when they change the year and/or month it does a requery to match.
I was trying to figure out how to return all records, by putting an all in each of the combo box tables, but am unable to figure out how to get my query to return all.
maybe just better to create a history form that shows all?
ok, so i changed my where statement to LIKE instead of = then put * in both tables to pass to query and it works, except for on little quirk. it reordered my months *,1,10,11,12,2,3,4,5,6,7,8,9 how can I get it back to normal counts? I tried passing the actual word but it didn't like that
Create one more column in your query. Do not include this new column in the output of the query, it will only be used to sort by. Whatever the name of the month column is, (I'll use mMonth) you need to use the Val() function to turn the month number into a number. If you have a month that is an astric (*) I'm not sure what you will do with that. But if all your month numbers really are 1, 10, 11, 12, 2, 3, 4, 5, 6, 7, 8, 9 then you will need this in your Field area: Expr1: Value(mMonth) and be sure to set this field for no output, and to be sorted, be it Asending or Desending is of course up to you.
I guess I wasn't clear. the output of the query is fine.
it's the order of the items in the Combo box.
i.e. in the combo box when you click the dropdown you see
January
October
November
December
February
March
April
May
June
July
August
September
because of the numeric value assigned to each one.
ok, so here's what i did
created a new column and numbered the months
0-all
11 January
12 February
13 March
etc..
then I went to the combo box data tab/row source added that column as a no show and sorted by that one. probably not the best way but it's working.
Thanks
Actually, it is the same concept I showed before, except it looks like it needs to be done in the SQL for the combo box. Make the number a number by using the Val() function, then sort the months via the number rather than the "number" that is actually a string. The way the months were showing, Jan, Oct, Nov, Dec, Feb is how they would show when the month "numbers" are sorted alphabetically. Yes, your new way gives you want you want, but somewhere down the line, its going to make a mess of things, either for you, or for the person that follows you in your present job.
ok, tried it that way and it works to, I'll leave it like you suggested, thanks