How to use variable in Access Queries?

Something like this I think is maybe what you are looking for

SELECT *
FROM tblSORDERS
WHERE ((tblSORDERS.SS)=[Forms]![frmNameHere]![cboMonth])
ORDER BY [SORDERS].[SS];

 
SELECT      - a list of all the fields you need from the table
FROM        - the name of the table
WHERE      - here you want to say where the value in this table's field name = the value in the field on the form
                   By nature this can not be NULL otherwise there will be NO records returned. Meaning if the value in the field is Jan 2004 - then ONLY records with that value will be displayed in the results.
                   You can do many different things with the WHERE clause...
ORDER BY - well you got this figured

Is that helpful?
Are you running this query from a command button?


Cheers
Michael

Looks interesting, I will try but here is the problem.

I was not looking to return values = to NULL, but all values <> to NULL.

I would use something like >0 but some of the fields are blank, and some of the fields are text such as "M,W,F" where the person will be seen 3 times a week, as opposed to "23" for being seen on the "23rd" of the month.

So, there is not one value which will be matched. I will be running the query from the combo box "after Update" event, or maybe a button if not practicle. For now I just want to get the query to work at all.

I can get the results I want from this query, but as you can see, I am defining the field value in the query. I guess I am trying to avoid setting up a query for each month, try to keep the database files lean.

This query returns the results I need:

SELECT [SORDERS].[SORDERS], [SORDERS].[DOB], [SORDERS].[JAN], [SORDERS].[FEB], [SORDERS].[MAR], [SORDERS].[APR], [SORDERS].[MAY], [SORDERS].[JUN], [SORDERS].[JUL], [SORDERS].[AUG], [SORDERS].[SEP], [SORDERS].[OCT], [SORDERS].[NOV], [SORDERS].[DEC]
FROM [SORDERS]
WHERE [SORDERS].[JAN] Is Not Null
ORDER BY [SORDERS].[SS];

Now I just need some way to get the variable or combo box value into the
WHERE & SELECT statements, so it will pull only the values I want. This
query returns all of the records which have values in the JAN column, but
it also returns the rest of the years data. IF I can put in the variable into
the SELECT statement & and WHERE statement, it would only look up the
MONTH the user selects.

I hope I am being clearer, and not repeating myself.. thanks for you time.

Something like this would be great if it worked:

SELECT [SORDERS].[SORDERS], [SORDERS].[DOB], [SORDERS].[Form!cboMonth.text]
FROM [SORDERS]
WHERE [SORDERS].[Form!cboMonth.text] Is Not Null
ORDER BY [SORDERS].[SS];

Ooops! That is the exact same statement I out in my original post! arrg..

It seems like it should work, but I am sure it is a syntax problem, or a problem wher the query is not able to touch the values stored int he combo boxes the way I have it coded, or the way I am going about it.

Hi Mshine,

Just got out of a meeting at work...

Let me see if I get this...  If not I'll post a link for some help from other experts.

So the data you are extracting comes from several columns
JAN FEB -thru to- DEC
each of these columns contains variable data (nothing consistant) so as you posted earlier the column with JAN might have "M,W,F" OR "23" OR may be a NULL Value.

What you are wanting to do is select the Month JAN -thru- DEC AND the Value "M,W,F" OR "23" (and I got the part about IS NOT NULL)
Then produce some kind of report or Query result filtered on this  ie: JAN23

Am I getting this right?

Cheers
M

If I got this right, then you could add two combo boxes to your form.

The first one to pull the month JAN thru DEC
The second to pull the data from the column

Then add a command button, OR after update event to the second combo box, to run the query.

What you think bout that?

The database is for a laboratory that visits Longterm care facilities.

Every month they will produce reports for the month. So they will produce
a report which will display all of the schedules for test that will be coming
up for that month. So I need the database to return ALL values for a selected
month (i.e. JAN), but not the results for the other months.

The dates are different, so I cannot specify JAN 23rd... I want the query
to search the JAN column and return ANY result that is not empty or NULL.

IF (and that is a big if) I could get the LAB to enter specified dates, it would
be much easier, and this would not be an issue, but sometimes they will enter
M,W,F or just M or just F meaning every Monday, Wednesday, Friday, etc.. for
that particular month.

I know exactly what I need to do, I just cannot seem to get the value that is
in the combo or text box into the actually query. I do not know how to get the
query and the form to touch each other, or make the connection during the
query, in order to pull the value out of the control, and use it in the query.

Here is a link to the database I am working on. It is in the very early stages
and if the code is not clean, it is because I have not cleaned it up yet. Maybe
if you downloaded and looked at it, it might be easier to see what I am try
to do. Of course I had to clean out the patient database, because of confidential
information, but I put a few records in place. Try to tie a query into the TxtMonth
textbox. If you select a month from the dropdown list, it will populate the textbox.
The Main control form in this project it the "Reporting" form.
http://home.comcast.net/~mike.jackson3/ll.zip

Thanks again.

Hi Mshine,

I had a look at your app.  I'm guessing that you are developing the app to move away from excel completely?
If you wanted my honest opinion - I would suggest some changes to your datamodel/table structure.  It would be easier to manage your data etc...
However, now I am straying from your orriginal question....

Unfortunately, I have been told to show up to another meeting here at work so I won't be available for some time.
They did this yesterday to me and it seems I have had another project added to my already full plate.
I don't want to leave you hanging so...
I'm going to ask some of the other experts to have a look as there are folks here with more years experience than I.
They'll be able to help you much quicker than I.


Cheers
Michael

Try:
SELECT [SORDERS].[SORDERS], [SORDERS].[DOB], [SORDERS].[JAN], [SORDERS].[FEB], [SORDERS].[MAR], [SORDERS].[APR], [SORDERS].[MAY], [SORDERS].[JUN], [SORDERS].[JUL], [SORDERS].[AUG], [SORDERS].[SEP], [SORDERS].[OCT], [SORDERS].[NOV], [SORDERS].[DEC]
FROM [SORDERS]
WHERE  is not null an[SORDERS].['Forms!MyForm!cboMonth'] Is Not Null
ORDER BY [SORDERS].[SS];

If that doesn't work change the WHERE line to:

WHERE  is not null an[SORDERS].[ & 'Forms!MyForm!cboMonth' & ] Is Not Null

Michael I agree with you completely on the data model.

Unfortunately at this time I am not able to make these demands. Yes moving away from
Excel completely is the idea. Currently this lab uses Excel exclusively, and has four people
cutting and pasting schedules every month.  Can you believe this?

My wife receintly took on a different career in the company, and saw this, and new that
I could throw something together, just to convince these people, there is a much better
way to handle this data. Unfortunately, I have the idea's but the syntax has always given
me trouble.

Currently the lab has a seperate spreadsheet for each facility, and each spreadsheet is
broken down into subunits (wings or floors). Even the spreadsheets are a mess, and
the methods these people are employing are very sloppy and unprofessional. It takes
me about 30min just to properly format a spreadsheet, for import into the table. There
are currently aproximately 40-50 spreadsheets that will have to be imported.

Datamodel? Frankly it has been alot of trial and error up until this point. It has been a
learning process as I have done everything by example, trial and error, and research.
Unfortunatley I do not do this for a living and it has been a labor of hobby. I know that
the correct thing to do is to have the lab enter the complete dates, or setup programming
behind the database to figure out which dates are the M-W-F of a particular month, but
until I have a commitment on the labs part, I will not be busting my balls to figure that
out. This project is an ice breaker.

My biggest fear is that the methods I am using will not be sufficient once the tables are
fully imported. Once the database is complete, my choices of handling the data will make
the database too slow to be practicle. BUT, based upon my little knowledge I am pounding
away to learn this stuff, and hopefully will have moderate to acceptable success.

I tried first and foremost to seperate the data into relevant tables, and keep all of the repeating
data for the patients into one table. I think I have split it as best I could. The SORDERS are what
they call "standing orders" and each one has insurance codes "ICD9's" that accompany the test
that are ordered. Some patients may only have 1 order, some may have 4, some may have
none at all currently, but it changes, which is why I chose the table structure for the SORDERS.

I am open to any suggestions, and it's never too late as far as I'm concerned. I've already started
over about 6 times.. LOL I am persistent if anything.

I will try out the SQL query listed above, and if it works correctly will accept the answer promptly.
But who do I assign the points too?? "Michael" you have been so patient and helpful, and "GRayL"
provided the statement. Is there anyways to split the points? If this works I would be more than
happy to give all my points up!!! LOL (well maybe not all, I may need more sooner than I think!)

Thanks again guys,
I can't wait to give this a go..

P.S. Ain't syntax a bitch!? Then again it IS what keeps alot of people employed!!

Re-read your question and ask yourself who contributed to answering it.

Hi Folks,

Just checking my email this morning before I get deep into work.

Mshine - Please don't be offended but...  Please do NOT give me any points!  I did not provide anything to the solution!  I got called into a meeting yesterday and was not able to help.
If the solution provided by GRayL works then you should award ALL the points to GRayL .  It's a nice gesture on your part, however GRayL is the one who provided the solution and therefore deserves the points.

We are ALL here to help others which is what makes everyone here an Expert - it's not about the points.  I posted a link to this question asking other Experts to stop by and help you as I was going to be out for the rest of the day and did not want to abandon you.  GRayL was good enough to come by and provide you with a solution.


GRayL  - Thank you!  I appreciate you coming by.


I'm off to get buried in work for the day...
Happy Databasing!

Cheers
Michael

No offense taken. I appreaciate everyones help.

Unfortunately the script did not work, or I did something wrong?

GRayL, did you by any chance grab the copy of the database I'm working
on? I may have done something wrong, but I created a query using the
script you provided above:
______________________________________________________________________
SELECT [SORDERS].[SORDERS], [SORDERS].[DOB], [SORDERS].[JAN], [SORDERS].[FEB], [SORDERS].[MAR], [SORDERS].[APR], [SORDERS].[MAY], [SORDERS].[JUN], [SORDERS].[JUL], [SORDERS].[AUG], [SORDERS].[SEP], [SORDERS].[OCT], [SORDERS].[NOV], [SORDERS].[DEC]
FROM [SORDERS]
WHERE  is not null an[SORDERS].['Forms!MyForm!cboMonth'] Is Not Null
ORDER BY [SORDERS].[SS];

If that doesn't work change the WHERE line to:

WHERE  is not null an[SORDERS].[ & 'Forms!MyForm!cboMonth' & ] Is Not Null
_____________________________________________________________________

Of course I placed "REPORTING" which is the name of my form with the control
I wish to reference, in place of "MyForm", and added [SORDERS].[SS] to the
SELECT statement (as it is reference in the ORDER BY).

The result was a "Missing Operator" error pointing to the "is not null an" portion
of the WHERE script. I tried various things in thought that maybe something was
left out or added by accident by to no avail.

If at all possible, could you please download from the link above, this small copy
of the database, and see if you can get you query to run against the SORDERS
table?

I'm pulling hair at this point, and just can't see why this is not working.

Thanks
Mike

It might be helpful to have a sample of the end result you were looking for.
What would the results of the query look like?

Is this something like what you are looking for?

SS              SORDERS       ICD9      Month
000001234        Blood             3212      Feb 12
000001234        Lipids             2312      Feb 13
000002223        Bloodwork      23223     Feb 13
000002223  NCHP             4019       Feb M
000002223        Bloodwork      23223          Jan 13
000000001        Test Order 1  401921   Jan 21
000002223        NCHP            4019       Jan M
000001234        NCHP            4435       Jan M,W,F
000001234  Blood            3212        Mar 12
000002223        Bloodwork     23223      Mar 13
000002223        NCHP            4019        Mar M

Cheers
Michael

Actually more like this:

If the control value is for "JAN"

SS              SORDERS       ICD9      JAN
000001234   Blood            3212      12

If the control value is for "FEB"

SS              SORDERS       ICD9      FEB
000001234   Blood            3212      M,W,F
000002223   NCHP            4019      12

Etc.. Then I would combine the values of the query, and match them
to the corresponding patient data for the final REPORT that would be printed.

The end result is to compile monthly reports quickly and easy.

The brickwall that is in front of me right now, is not being able to
use a variable to dictate which column will be queried.

I don't have any hair left! Firstly, DOB is not a field in SOrders, it is in Patients. Although abbreviated month JAN, FEB, etc, shows up in cboMonth, its actual value is the number of the month so it can't be used in that form to refer to a month.

 Are the numbers in each of the months the day of the month a test was scheduled, the days of the week in a month that the test was scheduled, or the day of the week over several months that a test was scheduled?  When you say "The people I am working on this database for, have taken it upon themselves to work all of these year in spreadsheets...", are you trying to get the data into a DB? How many years, how many spreadsheets, how are they differentiated and what the the typical size in records?

If the month values are dates, then it might be straightforward to create one large table from the several excel files you have - in VB. First put the excel files in one directory. Link to the file using VB. Open your DB file, move to the bottom, append a record, insert the first 3 fields and using logic create a date.  Move to the next month for the same SS and Sorder, create another date, append a new record, insert the first three fields, and move on. Loop thru the year, loop thru the records, and move to the next spreadsheet. If there are thousands of files, maybe you should build a table for each area that contributes. Suffice it to say your data is in a format that you would get if you did a crosstab query on a table. You are going to have to build the database correctly, albeit from poorly contstructed Excel spreadsheets. Advise if you think this is achievable.

 

Michael: Have a look at his sample DB at:

http://home.comcast.net/~mike.jackson3/ll.zip

I'm sorry, I must confess this is beyond me...

I had a better look at the database as GRayL suggested and will have to go with my previous comment where I suggested re-developing the datamodel/table structure.
If I read GRayL's post correctly I think he is suggesting the same.

I wish I could help more but I think this is beyond me.

GRayL: Thank you again for coming to help Mshine.  You have much more experience than I so I leave Mshine in your very capable hands.


Good Luck with the App!
Happy databasing!
Cheers
Michael

GRayL, I have to say that I did not think it was going to be this much trouble
to get and answer. I thought it was just a matter of me not knowing the
correct syntax.

I gave you the field DOB, because everyone knows what that is, whereas if I had
said "ICD9" I thought it would ilicit misdirected questions. It wasn't until after going
back and forth I decided to provide a sample of the database (paired down as it is).

The thing that drives me nuts is I could probibly make this connection with about
10min of programming in VB, but I wanted to do this in ACCESS, because there is
so many other features already built in, Reporting etc.. and the customer if needed
would be able to modify in the future if they wanted, without dealing with VB source
code.

There is alot of data, but not an overwhelming ammount. Approximately 8000
Records (patients) in total accross the 40 spreadsheets.

The values in the Month columns when numeric are the specific date/day for the month
and sometimes a patient has to be drawn 3 times a week ergo M,W,F. I totally understand
that I should push the correct usage of the dates for scheduling, and only have one column
labelled date, and have the data entered as a true date value.

I am also aware that the combo box can return a numeric index value (e.g. Jan=0), that
is why initially I tried to reference cboMonth.text, but as you know that would not work.

Maybe I should reference the text box on the Reports form? That is why it is there. I
was trying many approaches, which is why when you select the month on the reports
form, the textMonth value is set to the selected text

Believe it or not, I had all of this data in one table, but because one patient, may have
5 standing orders, this would mean I would have to list all of the patients static information
5 times as well. This also makes it impossible for me to use the patient SS as a key. Another
concern was that 4000 records at 20+ columns might not run very efficiently. I know it is not
that much data, but my ideas might not be the most efficient either. I really thought seperating
it as it is right now was the best thing to do.

I just can't understand why it seems so difficult for me to pull the text value out of a control
and have it used in the query. Maybe I should just try a different approach.

Understand that any data conversions or changes will have to be made by me, these users
are not techies, and everytime something changes in their little world, it throws them for a loop
and they are resistant. Why else would they still be doing this in spreadsheets.

Okay. That is still not a lot of data. 32,000 records is peanuts. Stick with me and I'll get you there. Got to go as "she who must be obeyed" is beckoning. See you tomorrow.

Damn GRayL, you a good.. Thanks for at least responding so quickly. It's nice to have someone online checking in.. There have been times when I post a question, and get no response, and then a month down the road, someone repsonds, by then I've already moved on.. LOL

BTW I am not entirely against each facility having it's own table either, if that is a thought.

It is something I have considered. This office has about 3-4 people working on these spreadsheets, and each of them handles their own share. If it is more efficient to
have these people connecting to seperate tables and queries, that could be an option
as well.

One thing I am not sure of is how this will be handled on their network. I know it will
not be run under SQL server or 2000 Server. I believe the office will independently handle
this through a workgroup type network.. Yes that's right.. and WORKGROUP!! LOL Some
people actually still use these in places other than a home network.. HAHA

Looking forward to any ideas you may have. Mshine

Mshine: Just back from a funeral. I hear what you are saying. When you import the Excel file this is what you get, right?

[SORDERS].[SORDERS],[SORDERS].[ICD9], [SORDERS].[JAN], [SORDERS].[FEB], [SORDERS].[MAR], [SORDERS].[APR], [SORDERS].[MAY], [SORDERS].[JUN], [SORDERS].[JUL], [SORDERS].[AUG], [SORDERS].[SEP], [SORDERS].[OCT], [SORDERS].[NOV], [SORDERS].[DEC]

Numbers in any of the months mean a date.
A letter in a month means that day of the week for every week in that month.
A series of letters in a month is the first letter of two or more days of the week for the whole month.


Thus, 21 means twentyfirst of that month
M in Jan, Feb, and Mar means every Monday in those three months
M,T,W in a month means every Monday, Tuesday, and Wednesday of that month.

How do you distiguish between Tue and Thu?

Distinguish between Tuesday and Thursday,,, hmmmm I'm not sure
how they do that, maybe Th for Thursday?

Not sure how they do that, to tell you the truth I have only seen M,W,F so
far.. hehe.

Actually the spreadsheets I import have ALL of the patient and sorder data
combined, as well as some other stray crap I edit out or add, to format it
for import as a table.

Then I run SQL queries to pull out the data into seperate tables.
I run a SELECT DISTINCT records to remove instances of duplicate
patient records and information as well.

Here is a sample of the spreadsheet format, not much smaller and
with generic data entered where names and SS# are in place. The
page is split into two parts Part1 is the spreadhseet before I touch it,
Part2 is what it looks like after I have formatted it for import:

http://afsg.whatzit-inc.com/bbs_images/example.htm

I actually do alot of editing to get the table ready for import. I change
the column headers because, some of the spreadsheets are titled
a little differently, or someone might format the ICD9 column as a
Date when it is really an insurance code. I remove all blank rows,
and place the patient information onto every row that has sorder
data. For example look at the last patient listed.. He has 4 seperate
orders that are drawn during the year. All on different dates.

Note that I had to add the MD2, INS2, and INSURANCE2 fields because there
are some patience that carrier secondary insurance (eg. Blue Cross & Medicade, etc..)

Theie spreadsheets had no column heading for the patients diagnoses/condition.

Alot of things I modified, just to make it easier for me to work with. I'm not sure
pulling data out of the spreadsheets if feasable, just because these people were
inconsisitent with the formatting of their spreadsheets, and column headers.

The data column are pretty much all in the right place, but it appears as though
each user has slightly changed things over time, either by accident or on purpose.

As previous quesiton asked was how long will they be tracking these records?
Basically the office only deals with 1 year at a time, adding and subtracting
Patients and Orders as the year passes. I do not believe they have historically
kept past records on hand.

Hope this helps.

GRayL,

I don't mean to be a pain in the butt, but I am not sure which
SQL query to use?

Everyone I tried did not work? I know I am supposed to put in
place the code in the code window, but I'm not sure how the
SQL query should be written to accept this code?

Do I simply put any query the works in, and name it QRY_MONTH,
because the above code will change it anyways?

You had a query in your mdb QRY_JAN which returned the records you were looking for for the month of January. It was a stored query - and when you retract it using myqd, then myqd.sql returns the string for that query. You did not have to do anything. Once you imbed the code in the onupdate event of the cboMonth control and run the form, when you select a month the onupdate fires, and my code fetches the SQL string, and modifies it with the selected month. Now when you run the query QRY_JAN it returns the values for whatever month you had selected in cboMonth. That is why I said change the query name to QRY_MONTH. If QRY_JAN runs correctly, change the name, insert the code, run the form, select a month, and then run the query.

In the query you gave me JAN was located 17 characters past the beginning of WHERE. The replace function picked up that String, searched the SQL and replaced every instance of Jan ( or what ever was the last selection) with the month string from the combobox.

This is what you sent me:

SELECT [SORDERS].[SS], [SORDERS].[NAME], [SORDERS].[DOB], [SORDERS].[JAN]
FROM SORDERS
WHERE [SORDERS].[JAN] Is Not Null
ORDER BY [SORDERS].[SS];

Create another query - QRY_MONTH with this SQL string and do not change anything.

Ok.. That is what I was hoping you'd say.. At first
I wasn't sure that was possible. It seems like a very
complicated way of going about it, but I will give it a try.

Will let you know as soon as I have had a chance to work it
in.

Thanks again for speedy responses, you've already tought me alot!

Well GRayL, it works like a charm! Thanks alot!

Now I just realized that the reports will have to be
generated Dynamically too! LOL

Since the value of the QRY_MONTH is always changing
predesigned reports will not work. Well off to the research
board again.

I am going to try and incorporate some of the code you
have shown me into something to deal with the reports.

If you have any suggestions, I'm all ears, or eye's in this case.

Here's the 250 I owe ya! You definitely earned it on this one!

Thanks again!
Mike

Thanks Mike. Glad to be of some help.