Dates, Forms, and Queries

[Ace2014]

Hi Everyone,

I've had some success building my multi-criteria selecting interface for my Access users.



I've been repeating the same process over and over, but I've finally gotten to my criteria that are a little bit tricky.

Check out my process and my challenges.

Process
I have an "inputTable" linked to a form where the user selects limited information from the "knowledgeTable"
I then have a command button calling macros to run a premade query linking the "inputTable" with the "knowledgeTable"
This creates a one to many relationship where I can choose to only have information selected where the "inputTable" value is equal to its corresponding column in the "knowledgeTable"

Challenges:
1) Interestingly, with the command button, I can no longer enter multiple information into my inputTables and eventually I will need to be able to.
I'm a bit stuck on that.

2) Also, I need to allow the users to input a date qualification. Meaning select no data with dates older than xx/mm/yyyy
I'm not sure how to do this either.

Thanks for any help with this

Let me know if it's not clear or if you have any questions.

[ranman256]

You will need a txtDate box to enter the date, but then 2 queries...1 w a date, 1 without.

if isnull(txtDate) then
  docmd.openquery "qsGetDataNoDate"
else
  docmd.openquery "qsGetData1Date"
end if

Re: Challenge #1: is the table keyed so you cant enter >1 record / key?

[Ace2014]

Date:
So can I open a query and type that in the SQL section? Updating the names (txtDate) would equal the column in my table.
The txtDate box can that be on a form or is it supposed to be a message box? I don't know how to do the pop-up input message boxes yet, although that could be really cool.

Multiple input values:
I don't believe so. I mean before I added the button that runs my select query based off the new criteria inputted by a combo box, forcing the users to select what they want, into the table the form is linked to, I could add multiple criteria.

I have 2 rows in my table, but the first one changes over and over again.

[lfpm062010]

It is not clear to me how you use your input table. It is also not clear how your form was build and why can't you no longer enter multiple information into my inputTables.

Can you provide example how you use the form and input table? and what you want the form and input tables to do?

It also would be easier if you provide the Access database for further analysis (like what the form looks like and what the design of your input tables looks like, etc).

[Ace2014]

I can send you my database if you want I'm still constructing it, so there's no confidential information, nor is it that heavy. If you could just inbox me with an email address you'd trust me to have.

My knowledge of VBA is rather limited, so what I'm doing might not be the best way of doing it.

Basically, I have a knowledge database that has different levels of segmentation.
I need the users to be able to narrow the scope of the data in an easy way that will also prevent them from ruining the database.

So, I've been using forms
Form_Select_Level1 asks for the highest level of segmentation
(The form was created by having the Select_Level1_tbl selected and creating a form. I changed the type box to a combo/list box that will only let the users select criteria at Level1 from the knowledge database.)
The user selects there information (before I added the command button, they'd press "return" to get their information inputted into the Select_Level1_tbl.
Then I have a query connecting the Select_Level1_tbl with the knowledge query (qry1_PartPricingAggregation)
My button on the form, hopefully reruns the query, closes the form, and then opens the next level of information that needs to be selected...you guessed it...Level 2 lol

I want only the Level 2s linked to Level 1 to be displayed, so I have to connect this form to the query I just ran.
I repeat those steps for Level 2 and Level 3.

Then I need the users to be able to select the oldest date they will accept, different brands, and different markets.
*The way I've been doing, I'm not sure how to ask for "select dates > than x"
**There can be multiple brands and I need to have them inserted into my table, as well as with the markets.

[lfpm062010]

You can also use this method to attach to this post or I will send you a private IM for you to send to.

To provide db: Make copy, remove confidential data, run compact & repair, zip if large - 2mb allowed, attach to post. Attachment Manager is below the Advanced post editor window

[Ace2014]

I think I'd just prefer to email it to you. If that would be ok

[Ace2014]

You will need a txtDate box to enter the date, but then 2 queries...1 w a date, 1 without.

if isnull(txtDate) then
  docmd.openquery "qsGetDataNoDate"
else
  docmd.openquery "qsGetData1Date"
end if

I was looking at this a little bit more. Does that mean that if there is no date it will return all the data? and then if there is a date, it will return all the data with dates greater than the inputted date?

[ranman256]

Right, no date = a separate query. You cant ask date between null and null.
1 qry with dates too.

[hapm]

You can check the control value for being null as an or condition next to the between condition and you will need only one query.

[Ace2014]

Well, I won't have any null dates.
But I don't understand how to write the querry that would return databaseDates > inputMessageBoxDate
When Run, query gives dates greater than inputted date

[hapm]

Well, I won't have any null dates.

So the user will always give a date as search criteria?

[ranman256]

under the date field,in the query criteria, put in brackets [Enter Date]

[Ace2014]

So the user will always give a date as search criteria?

Oh, no, the user can leave the date filter blank. Ok, I see what you mean.

But you wrote commands, I still have to construct those queries right?
I don't know how to construct a query adding the inputted dated from the form into the query
I can create a query saying if isNull, return all, but then I have to assign a variable to my input box. I'm not very good at coding yet.
So for example, if my combo box's name is "inputDate" the code might look something like: ? ? ? help?

Function queryDate ()
Dim Date string (format:mmm/YYYY)
If inputDate isNull;
Query = return all values;
false
Query = dates > Date
End Sub

Then there would be a command attached to the inputDateBox and it would run this macro

CommandX_Update ()
Call queryDate
End Sub

[Ace2014]

under the date field,in the query criteria, put in brackets [Enter Date]

I don't have a query attached to it yet.

I have like 5 filters and the date I need the user to choose a date and then it filters the attached query on values greater than inputted date.
It's not a combo box, so no query is attached.
Do I attach a query to Event: After Update, but then I'd need to code to create the query.

Will the form continue to update the other filters as well if I make a special query for the inputDate