Report of fiscal year and year to date

[Access N0vice]

I am trying to create a user input form that will take date information from a user and give a report that is based on the starting date and ending date. I have the form working but I need a "between" statement or something that will take the input information. I have tried this in my query and it doen't seem to work. Between [Forms]![DatesearchForm]![StartingMonthdayyear] and [Forms]![DatesearchForm]![EndingMonthdayyear]. Is there something else I can try? Am I totally of track here? Any help would be appreciated.

Thanks Forum

 

[Beetle]

What do you mean by "it doesn't seem to work"? Are you getting an error, or just no records returned? It looks correct as you posted it, assuming that the form and control names are correct and the users are entering acceptable date values in the form. You could also try;

>=[Forms]![DatesearchForm]![StartingMonthdayyear] And <=[Forms]![DatesearchForm]![EndingMonthdayyear]

 

[boblarson]

What is the date information in the field stored as? What datatype is it and what would the values look like in the table?

 

[Access N0vice]

I am getting records but they are not between the specified dates. There are no error messages. I am using a short date for the field named "StartingMonthDayYear" data mm/dd/yyyy and the same for "EndingMonthDayYear". In my drop down I get the dates in the records to select so I am picking the first record date which is 01/05/2010 and the ending record for the year which is 12/27/2010 and my results are all records I have in the database which 1/05/2010 and 03/15/2011. With my test I set up fields in the query that show me that I am getting the entered data. I hope this helps you understand what is going on.

Thank you for responding.

 

[boblarson]

Any chance of posting the db? I am not sure what it would be.

 

[Access N0vice]

I have never posted a db do I just attach it to a message?

 

[boblarson]

I have never posted a db do I just attach it to a message?

See here for how:
http://www.access-programmers.co.uk/forums/showthread.php?t=140587

 

[Access N0vice]

Ok Bob,
Here is the db.

 

[boblarson]

Sorry, it looks like it will be quite a while until I can look as it would appear that the computer here at work that I use for running Access 2007 is no longer available to me or it is shut down. I don't have any other method for opening accdb files until I get home.

 

[Access N0vice]

No problem, I do really appreciate you taking the time to look at it.

 

[boblarson]

Okay, you had several things wrong.

1. The row source for the date combos doesn't come from the query they come from the table (see the row source for the combos to see what they should be.

2. You had the start date and end dates as FIELDS in your query instead of CRITERIA on the DATE FIELD. See how I modified your query.

 

[Access N0vice]

Thanks for the insight on this. I see now what i was doing wrong now. I forgot to put my between statements back into the query after I removed them. I had been looking at them for so long I didn't even notice until you pointed it out. Thanks again.

 

[Access N0vice]

Is there a limit to the number of selections and buttons that can be put on a form? I was moving happily along and can get 3 groupings to work as soon as put a 4th grouping on the form I get a message about it "#4" being to complex. The other 3 still work fine.

 

[boblarson]

A form can have 755 controls over the lifetime of the form (it increments each time you add one regardless of if you remove it too) but you can usually reset that by exporting your form to another database and then reimporting it.

However, your error is not related to that. What "grouping" are you looking to do? And what are you using? Is it an option group? Are you trying to use an option group (which returns a number) on a text field?

 

[Access N0vice]

I thought that the group statement I made might confuse the issue. what I mean by that is if you where to take the little test db and put two more sets of drop downs and buttons to reference more tables and queries.

 

[boblarson]

You can do a lot with that. There are a lot of options you can choose to do.

 

[Access N0vice]

Ok I figured out that I had spelled one of the field name incorrect I fixed that and it is working great thanks for watching this and all of your help.

 

[Access N0vice]

Bob,

If you are still watching this thread I have another question.
I want to add another selection to the drop downs one that will specify a location that will sometimes use the date selection and other times not use the date selections (meaning that the dates would be blank) so I would get all of the records for locations from the beginning of the database until today. do I need to use a seperate form or query for this? or can i use the same query?

 

[boblarson]

I can't remember what we did to get your other stuff to work and the computer here at work which had 2007 on it I don't have access to anymore as they had to use it for a new employee. So, did we build the where clause via code? If so, you should be able to modify it so it doesn't matter if a date is entered or not entered.

 

[Access N0vice]

I have it built using combo boxes and input from the combo boxes fill a between statement in a query to get the records so I have added another combo box for a location that also is in the same query to get records based on location but when there is nothing in the date combo's I don't get any records. I hope this clears it up.
It does work fine when I have selected dates.