Access help :(

[dslegends]

Ok not sure where to start here but i need some help on access.
Heres what Im trying to do.



I have imported and Excel Sheet into An access database - Now i need to create a user friendly way of searching through that Excel sheet so that it will display just the persongroup that i searched for and no other data and display that data but i have found nothing after googleing for several hours...and im drawing a blank after not using access for a good 5-7 years any help would be grand im pretty desperate thanks

[rpeare]

create a query based on your linked excel spreadsheet, in the criteria of the PERSONGROUP field put in [Enter the string you're looking for]. Then run the query, a dialog box will pop up saying 'enter the string you're looking for', type in what you want and away you go.

[dslegends]

ok what im trying to do is actually make this Oncall tool user friendly as well so all the users have to do is type in a search box the persongroup they want to see and it will display the oncall people for the certain group in a display box that pulls the info from the excel sheet sry i really have drawn a blank on this stuff

[rpeare]

there's basically no difference in what you are currently doing and what I am suggesting.

Your current setup:
Person clicks a button
Person Enters a name
Query results are displayed

My suggested setup:
Person Enters a name
Person Clicks Button
Query results are displayed (if there are records)

You'd just be changing the order of your process nothing else.

[dslegends]

Ok i got my original question fixed. but im still unable to get it to where the user can just type inside a form that i have made and hit the button and it displays the info in the textbox i have set up

[rpeare]

Can you post an example, I'm guessing you do not have your criteria set up correctly but 'it doesn't work' isn't helping me identify the problem. If you post a sample please make it 2003 or below.

[dslegends]

ok when ever i save it to 2002-2003 verison it removes everything so here is pictures

[rpeare]

in the DBList Query you want your criteria to reference the unbound box right next to the ON CALL button (I am assuming this is where you are typing in your search string)

so instead of saying [Enter the string you're looking for]

the criteria will, instead, say

forms!formname!searchfieldname

where formname is the name of your form
searchfieldname is the name of the text box right next to your on call button.

[dslegends]

ok i put that in there the name of my text box is just Text1 and then the form name which is On-Call UI and it brings up but when i go back and try and search for another field it holds on to the previous data and i have to close th query out just to get it to clear and how can i get it to display that query information inside the big textbox that i have placed there?

Man, I thank you so very much for your help!!!!!!!!!!

[rpeare]

if you are updating the unbound box below your ON CALL button you would have to refresh it

with something like

me.BigBoxName.requery

if it's a listbox or subform or something similar.

[dslegends]

Ok now I have that running properly now i just cant figure out how to get lets say for this day these people are on call at this certain time. and when its within that time frame i need it to display only those people. how could i do that?

[rpeare]

I don't understand the question as you have posed it.

Do you want to search your on call people for a specific date and a specific time period?

Are you storing when people are on call down to that level of detail? Or are you storing their on call times by a shift number or other indicator?

if you're looking to search for anything between a range of values you use the BETWEEN criteria

For instance if you wanted to find someone that was on call at any time between noon and 5pm it's actually 4 different searches

1. Does their shift start before Noon and end during the search period
2. Does their shift start during the search period and end after 5:00PM
3. Does their shift start before and end after the search period?
4. Does their shift start and end in the search period

This example assumes you're keeping track of an start and end time of each shift and that you're collecting your shift search period from the same form you have posted. We can also make some logical reductions so we don't need four sets of criteria

create your query and in the first criteria line you would have:

for starttime: between forms!<yourformname>!<starttimefield> and forms!<yourformname>!<endtimefield>

for the second criteria line you'd have

for EndTime: between forms!<yourformname>!<starttimefield> and forms!<yourformname>!<endtimefield>

for the third criteria line you'd have

for starttime: <= forms!<yourformname>!<starttimefield>
for endtime: => forms!<yourformname>!<endtimefield>

There may be more elegant ways of setting up your criteria and someone can post them if they have a better method.

[dslegends]

yes that is basically what im trying to do, but when i put in
between forms!<yourformname>!<starttimefield> and forms!<yourformname>!<endtimefield>
for my start time in the same query that im trying to use in the above posts it gives me invalid expression unless im putting it in the wrong section

[rpeare]

You have to change <yourformname> and <yourfieldname> to the actual table name and field name that are in your database.

For instance if your form is called frm_Search and the field on the form you are using as your criteria is fld_Search then the criteria of your query would be

forms!frm_search!fld_Search

[dslegends]

sry for the short post last i actually got distracted and accidenttly submitted it
when i did fill in the form and field is still get an error "The expession you entered contains invalid Syntax" im putting this under also the On Call StartDate field.

Between forms!On Call StartDate And forms!On Call EndDate

and also when ever i click the button it will display everyone from the person group but it will automatically jump over the the table(excel spreadsheet) in access