Maybe its not possible?

[mnickell]

I have created a form that will run a query for my company interentiry transactions reports. I have to be able to pull an unsure amount of Entities over a range of time. Ex. I need to be able to enter Entiy 1000, 1008, 1009 and possibly more or different entitys from lets say January to March and then run a report off of that query. I can get the Entity part to work if I do filter by form, but not the dates, and if I do not do filter by form I can get the dates to work but not the entities. Is it possible to do both of these things at the same time?



Thank you for any insight possible

[ranman256]

If you also want to do both and 1 filter of each...
Filters:
dates only
entities only
dates and entities

The filter may have to do some logic when user clicks a 'go' button:

select case true
   case not isnull(txtEntity)  and  not isnull(txtStartDate)
      vFilter = "[entity] ='" & txtEntity & "' and [date] between #" &  txtStartDate & "# and #" & txtEndDate & "#"

   case not isnull(txtEntity)
      vFilter = "[entity] ='" & txtEntity & "'"

   case not isnull(txtDateStart)
      vFilter = "[date] between #" &  txtStartDate & "# and #" & txtEndDate & "#"
end select

me.filter = vFilter
me.filteron = true

[mnickell]

Ill be completly honest I don't exactly understand how all of the logic and coding stuff works as I am an average access user on my best day. Could you possible expand on the logic slightly more so that I have a boarderline idea of what I need to do?

[June7]

Review:

http://allenbrowne.com/ser-62code.html

For selecting multiple Entities, review:

http://allenbrowne.com/ser-50.html

[ranman256]

If you are filtering your data based on what the user picks on the form,
then you need a button to execute the filter.

The filter may be:
date only,
entity only,
or both, so the code does this.

On the button _afterUpdate event, paste this code in it.

[mnickell]

I appologize I am still having trouble getting it to work properly. Should I just create a new button and paste that code into the new button

[mnickell]

Or is there anyway you could try and create what you are talking about and reattach a new file

[ranman256]

EnitiesExample.zip

Examine the Entities test attached.

[mnickell]

Thats close to what I need. I think my database has something similar currently. Im not sure if I was doing it wrong or what but I need to be able to select the different entities first and then by date. Not find the entities that fall within that range of dates.

[June7]

What does that mean? You want criteria to allow for multiple entities and multiple dates? So a record must meet the entity and date criteria? Like:

WHERE Entity IN (1000, 1008, 1009) AND [Datefield] IN (#1/1/2014#, #2/1/2014#, #3/1/2014#)

[mnickell]

Yes. Basically I need one form with a multi-select to pick and chose what entities I need to pull up for my report and then also need to be able to chose the dates for those entites. The format of the Dates (1/1/2013) technically are just an entire month there is not day-to-day dates. They could just say (Jan, Feb, Mar). It was the format I was given but can be changed.

[June7]

Sounds like you need 2 listboxes and the code described by Allen Browne in the second link I referenced in earlier post.

[mnickell]

I think (not sure) I would be able to just use a simple where function if I only needed to use the form once but I need to be able to go back and use it multiple times and select different entities (which could be more or less entities) over a possibly different range of dates

[June7]

Then more like:

WHERE (Entity IN (1000, 1008, 1009) AND [Datefield] IN (Jan, Mar)) OR (Entity IN (1010, 1019) AND [Datefield] IN (Aug, Nov, Dec))

That's getting really complicated and beyond anything I've ever tried to program.

[mnickell]

Ok so yesterday I had the form set up to run the between function on the date field and it was working but my problem was being able to use the multi select box as criteria for the entity field at the same time. There has to be able to run the query that way correct. The two criteria dont have to be based in the same field I just need to be able to run them both at the same time. I can use the between function and pull up every entity between the range of dates I need but I need to be able to also use the multi select box and get only the entities I need. I dont know if that helps or not. I think I may be making it sound like more of a complicated problem than it probably is because I am not the greatest with access