Setting query criteria in a form and running a query from that form by pressing a cmd

[artromanov]

Hello everyone. I am somewhat new to access and this forum. Hopefully it will be a great and helpful experience.

My problem is this. I have a database (about 65,000 records).

I get a run from another party every month in a text format (notepad) which I import into my database and make a comparison between the previous months and current months tables (some fields) by running a query. What I am trying to do is create a nice form that will

a) have an option to import the the file automatically (it gets updated every month in a file on the local network [same name] now I have it linked and when it changes I run a query and save the table as the new month) I want to automate that. (if possible)

b) have a form that will alow me to enter different data (criteria) and after pressing a cmd button it will run a query for me. (This is most important) I tried to do something and something really crazy began to happen.

c) export or save that run to a specific folder and chosen name. (i do not know if it is possible, again automatically)
Whoever can, please help. All advice and help is very much appreciated in advance.

Thanks
-Arthur

[artromanov]

By the way, it is sort of like a Find A Person. In other words, if I enter many different criteria, it will give me the results in a separate form. If I enter a unique criteria, say Social Sec #, then it will pull up all the needed information just about that 1 person. Thank you alll so much..........plz help :-(

Hello everyone. I am somewhat new to access and this forum. Hopefully it will be a great and helpful experience.

My problem is this. I have a database (about 65,000 records).

I get a run from another party every month in a text format (notepad) which I import into my database and make a comparison between the previous months and current months tables (some fields) by running a query. What I am trying to do is create a nice form that will

a) have an option to import the the file automatically (it gets updated every month in a file on the local network [same name] now I have it linked and when it changes I run a query and save the table as the new month) I want to automate that. (if possible)

b) have a form that will alow me to enter different data (criteria) and after pressing a cmd button it will run a query for me. (This is most important) I tried to do something and something really crazy began to happen.

c) export or save that run to a specific folder and chosen name. (i do not know if it is possible, again automatically)
Whoever can, please help. All advice and help is very much appreciated in advance.

Thanks
-Arthur

[MMcCarthy]

a) have an option to import the the file automatically (it gets updated every month in a file on the local network [same name] now I have it linked and when it changes I run a query and save the table as the new month) I want to automate that. (if possible)

When you import the file next click on the advanced tab in the wizard and save an import specification file (remember the name).

Then you can code the following behind a command button

 
DoCmd.TransferText acImportDelim, "Name of Specification File", "TableName", "Full path to file", True
 
 

acImportDelim - means that the text file has commas (or something else) separating the values. If the file is Fixed Length then acImportFixed

Name of Specification File - As above

TableName - is the name of the table you want to import to

Full path to file - e.g. "C:\My Documents\sample.txt"

True - Means the file has field names on the first line (False if it doesn't)

b) have a form that will alow me to enter different data (criteria) and after pressing a cmd button it will run a query for me. (This is most important) I tried to do something and something really crazy began to happen.

Explain further what your query is ... and what the fields on the form are called and what values they represent. If you have a sample query or piece of code post it here.

c) export or save that run to a specific folder and chosen name. (i do not know if it is possible, again automatically)

Its very similar to the import above. Do an export and click on advanced tab and save an export specification.

 
DoCmd.TransferText acExportDelim, "Name of Specification File", "TableName", "Full path to file", True
 
 

[artromanov]

Thank you for the help.! :-)

Although I am still quite lost, I need the form to be linked to a query where instead of me entering criteria into the design view of a query, I can use the input text boxes to enter the criteria and click a cmd button "Search" and the query will run. I am creating a more user friendly environment so that people other than me can use this at work without knowing much abouit access. I think it would be very helpful and beneficial to them. So basically my query is quite complicated. It has many criteria (although I just need the basic way to link the criteria into a forms text box and create a button that will run the query. the rest I hope I will figure out.

Thanks

[southoz]

Good ay ,

not sure if this is what u mean but it may help
i have a few fields on a form and a query for statements an invoices , on clicking a command button it open an invisible form that runs a query connected to a listbox

SELECT DISTINCT statement.OrderNumber,statement.clientname,stateme nt.pickupdate,statement.subprice,statement.orderwe ek FROM statement WHERE [statement.orderweek] = Forms![Orders1]![orderweek] AND [statement.clientname] = Forms![Orders1]![clientname] ;

the first few fields seperated with comers indicate the fields in the query i'm after the from statement (statement being the query itself) where the fields in the statement = whats on the main form AND as the second part of where > so if your looking for multi arguments for the query u can try that

hope it help a little
southoz

[artromanov]

Thanks, but where am I suppose to write the statement that you gave me: beginning with the select distict. Would it be possible for you to e-mail me the db with this example? I think it is something that I can use to build on?
If you can and dont have much info there that you cannot share (which I dont need) that would be cool. my e-mail is: ArtRomanov "AT" gmail "DOT" com

Thanks for your help.

Good ay ,

not sure if this is what u mean but it may help
i have a few fields on a form and a query for statements an invoices , on clicking a command button it open an invisible form that runs a query connected to a listbox

SELECT DISTINCT statement.OrderNumber,statement.clientname,stateme nt.pickupdate,statement.subprice,statement.orderwe ek FROM statement WHERE [statement.orderweek] = Forms![Orders1]![orderweek] AND [statement.clientname] = Forms![Orders1]![clientname] ;

the first few fields seperated with comers indicate the fields in the query i'm after the from statement (statement being the query itself) where the fields in the statement = whats on the main form AND as the second part of where > so if your looking for multi arguments for the query u can try that

hope it help a little
southoz

[MMcCarthy]

Firstly, if you are giving your email don't write it correctly write it like this

ArtRomanov "AT" gmail "DOT" com

Sites like this get trawled on a regular basis looking for legitimate email addresses.

Explain further what your query is ... and what the fields on the form are called and what values they represent. If you have a sample query or piece of code post it here.

I do understand what you are trying to do which is why I asked that you post your query as it stands at the moment along with a list of the criteria you want the user to be able to enter. I can't help without that.

[MMcCarthy]

Firstly, if you are giving your email don't write it correctly write it like this

ArtRomanov "AT" gmail "DOT" com

Sites like this get trawled on a regular basis looking for legitimate email addresses.

I've changed your post accordingly by the way ...

[artromanov]

Instead of replying, I have accidently REPORTED that message (which took me about 30 minutes to write) Oh no. If you are the moderator (or admin,can you please check your report messages?) I am so sorry.
Thank you.

I've changed your post accordingly by the way ...

[MMcCarthy]

This is the message that was posted ...

Thank you kindly. I have no clue how to paste the query. It is in design view (there is no programming or SQL where statements. (I dont even know how to use them)

Basically this is how it is formed. I have many tables that relate to one another based on many different codes. e.g. Budget Code, Payroll Distribution Code and so on... (about 20 different codes) There are 65,000 emplyees listed (many of them twice) because of changes made to their profile that are saved as and now overwritten. Every month, I get a new file with updated information about the employees. (The 65000 are employees dated like 20 years) So, there are only about 8,000 ACTIVE employees with a leave status B. What I do is compare, using a query last months and this months roster based on changes in their leave status (everyone going from a B to an A) as an example, the criteria set for August - September is : <>[September Employee Roster]![Leave] <-- this is written in the criteria of the August table:

What I would love access to do is do this automatically or semi automatically. Import the file, set up the query and run after I press a button.

This i believe would be impossible (atleast for me to do) but what is possible is the following:

I use this DB for other purposes also. I would like to create a form where a query will exist (in the background) and after I input some criteria (into the text fields) of the form and press a button, then the query will run and return results. Basically I want to make a search feature that will pull out records after I enter the criteria on a form instead of in design mode in a query. See if you understand what I mean. Basically for now, I just want to create a search feature.

P.S. Where do you write the where statements and syntax?Thanks

[MMcCarthy]

Thank you kindly. I have no clue how to paste the query. It is in design view (there is no programming or SQL where statements. (I dont even know how to use them)

Open the query in design view and then change the view to SQL. Just copy and paste it here.

[artromanov]

Thats pretty cool. Here is what I have.

SELECT [September Employee Roster].SS, [September Employee Roster].[Title Code], Title_codes.Decription, [September Employee Roster].[Last Name], [September Employee Roster].[First Name], [August Employee Roster].Leave, [September Employee Roster].Leave, Leave_Codes.[Leave Description], [September Employee Roster].BC, [September Employee Roster].BL, [September Employee Roster].Paycode, Pyrl_Dist_Codes.[WORK UNIT], Pyrl_Dist_Codes.[WORK UNIT DESC], Pyrl_Dist_Codes.[GEO LOC DESC], [September Employee Roster].[Salary/Rate], [September Employee Roster].Decimal, [September Employee Roster].[Emhist Date]
FROM (Title_codes INNER JOIN [August Employee Roster] ON Title_codes.[Title Code] = [August Employee Roster].[Title Code]) INNER JOIN (Leave_Codes INNER JOIN ([September Employee Roster] LEFT JOIN Pyrl_Dist_Codes ON [September Employee Roster].Paycode = Pyrl_Dist_Codes.PAYCODE) ON Leave_Codes.Leave = [September Employee Roster].Leave) ON ([August Employee Roster].SS = [September Employee Roster].SS) AND ([August Employee Roster].[First Name] = [September Employee Roster].[First Name]) AND ([August Employee Roster].[Last Name] = [September Employee Roster].[Last Name])
WHERE ((([September Employee Roster].[Title Code])<>80633) AND (([August Employee Roster].Leave)<>[September Employee Roster]![Leave]) AND (([September Employee Roster].Leave)="A") AND (([September Employee Roster].BL) Not Like "6***") AND (([September Employee Roster].Paycode) Not Like "******2" And ([September Employee Roster].Paycode) Not Like "******4" And ([September Employee Roster].Paycode) Not Like "******6") AND (([September Employee Roster].[Emhist Date]) Like "200609**") AND (([September Employee Roster].SeqNum)=1)) OR ((([September Employee Roster].Leave)="U"))
ORDER BY [September Employee Roster].[Last Name], [September Employee Roster].Leave;

[MMcCarthy]

Lets just look at the where statement as I assume this is where you want the user criteria to be used.

If for instance you set up a text box on the form called txtTitleCode for the user to enter the Title Code.

you could change this part of the statement to:

 
WHERE ((([September Employee Roster].[Title Code])<>[Forms]![FormName]![txtTitleCode]) 
AND (([August Employee Roster].Leave)<>[September Employee Roster]![Leave]) 
AND (([September Employee Roster].Leave)="A") 
AND (([September Employee Roster].BL) Not Like "6***") 
AND (([September Employee Roster].Paycode) Not Like "******2" 
And ([September Employee Roster].Paycode) Not Like "******4" 
And ([September Employee Roster].Paycode) Not Like "******6") 
AND (([September Employee Roster].[Emhist Date]) Like "200609**") AND (([September Employee Roster].SeqNum)=1)) 
OR ((([September Employee Roster].Leave)="U"))
ORDER BY [September Employee Roster].[Last Name], [September Employee Roster].Leave;
 
 

[artromanov]

You have perfectly understood the point. I will try this out and leave feedback about it. Thank you very much.

Lets just look at the where statement as I assume this is where you want the user criteria to be used.

If for instance you set up a text box on the form called txtTitleCode for the user to enter the Title Code.

you could change this part of the statement to:

 
WHERE ((([September Employee Roster].[Title Code])<>[Forms]![FormName]![txtTitleCode]) 
AND (([August Employee Roster].Leave)<>[September Employee Roster]![Leave]) 
AND (([September Employee Roster].Leave)="A") 
AND (([September Employee Roster].BL) Not Like "6***") 
AND (([September Employee Roster].Paycode) Not Like "******2" 
And ([September Employee Roster].Paycode) Not Like "******4" 
And ([September Employee Roster].Paycode) Not Like "******6") 
AND (([September Employee Roster].[Emhist Date]) Like "200609**") AND (([September Employee Roster].SeqNum)=1)) 
OR ((([September Employee Roster].Leave)="U"))
ORDER BY [September Employee Roster].[Last Name], [September Employee Roster].Leave;
 
 

[artromanov]

By the way, if I want the field to be a wildcard entered from the text box, how would I do that?

[artromanov]

Another thing: How can I tie the Command button to run the query based on what I inputted in the text box?

By the way, if I want the field to be a wildcard entered from the text box, how would I do that?

[MMcCarthy]

By the way, if I want the field to be a wildcard entered from the text box, how would I do that?

Using this line as an example and creating a textbox on the form called txtBL.

AND (([September Employee Roster].BL) Not Like '[Forms]![FormName]![txtBL]*')


BTW you only ever need to use one *.

[MMcCarthy]

Another thing: How can I tie the Command button to run the query based on what I inputted in the text box?

Just use the wizard to set up a command button to open this query.

[artromanov]

I am getting an odd message.

It reads: The text file specification "FISA Link Specification" does not exist. You can import export or link using the specification.

Sounds terrible

Another thing: How can I tie the Command button to run the query based on what I inputted in the text box?

[MMcCarthy]

I am getting an odd message.

It reads: The text file specification "FISA Link Specification" does not exist. You can import export or link using the specification.

Sounds terrible

You didn't save the specification using the advanced button on the import/export as explained in previous post.

[artromanov]

Everything works perfectly... Thank you so much and I am terribly sorry to be a pain in the behind.

Everything worked except for some reason if I do not enter any value (I do not want that value to be entered, the query turns out blank. Its Okay, thats tomorrows concern. Thank you very much.... I really appreciate what you guys do. I never knew that a forum can be so helpful and fun. Thank you - everybody and i'll see you guys tomorrow. :-)

You didn't save the specification using the advanced button on the import/export as explained in previous post.

[NeoPa]

BTW you only ever need to use one *.

Are you saying that "BlahBlah*" is equivalent to "*BlahBlah*" - or just that "BlahBlah*" is equivalent to "BlahBlah**"?

[MMcCarthy]

Are you saying that "BlahBlah*" is equivalent to "*BlahBlah*" - or just that "BlahBlah*" is equivalent to "BlahBlah**"?

Adrian

You're such a smart Alec, but you're right.

What I was trying to say was:

"A*" will give everything beginning with A
"*A" will give everything ending in A
"*A*" will give everything with A anywhere

What I was trying to say when I said you only need one * was
that one * will represent any number of characters you don't need a separate * per character.

Mary

[MMcCarthy]

Everything works perfectly... Thank you so much and I am terribly sorry to be a pain in the behind.

Never think that, its what we're here for.

Everything worked except for some reason if I do not enter any value (I do not want that value to be entered, the query turns out blank. Its Okay, thats tomorrows concern. Thank you very much....

we can help with that come back when you're ready

I really appreciate what you guys do. I never knew that a forum can be so helpful and fun. Thank you - everybody and i'll see you guys tomorrow. :-)

I'm glad to hear that was your experience. Creating a fun atmosphere keeps the experts interested as well which gets your questions answered.

[NeoPa]

Mary,

Although I like to have a laugh now and again, in this case I had guessed you meant exactly what you said.
I was just trying to clarify in case :-
1. Anyone else misunderstood
2. I found out that actually I was wrong in thinking what I thought.

I'm very pleased to say that I do regularly find out things I either didn't know or had not understood correctly while reading through these forums.

In this case I plead innocent truthfully - your honour.

-Adrian.

[MMcCarthy]

Mary,

Although I like to have a laugh now and again, in this case I had guessed you meant exactly what you said.
I was just trying to clarify in case :-
1. Anyone else misunderstood
2. I found out that actually I was wrong in thinking what I thought.

I'm very pleased to say that I do regularly find out things I either didn't know or had not understood correctly while reading through these forums.

In this case I plead innocent truthfully - your honour.

-Adrian.

Adrian

I was only joking

I responded to this post in a hurry and your point was a good one.

Mary