Hardcoded Parameters In Querys--can I Find All?
Jun 21, 2005
We have a tbldepartments. At this point (has been a few years)...many dept. have merged or changed names. We want to 'change' the names of some and delete or add others.
Ok on the delete/add.
However, in many of our queries, we have the dept. name/number hardcoded in. And trust me, there are tons and tons of them...this is a big database.
Short of going through each query, is there a way to 'search/replace' either in each query or in all? To update these easily?
suggestions?
View Replies
ADVERTISEMENT
Sep 24, 2007
I have created a cbo box that I am using to selects locations (citys) in a field. I have used "Distinct" to elimate the duplicates but I also want to elimate three locations. They are Verdun, Lasalle, and Montreal. In my query I have tried the following statement in the Critera location. Not "Verdun" Or "Lasalle" Or "Montreal". The query returns the following statement: <>"Verdun" Or "Lasalle" Or "Montreal" this statement does take out the first location "Verdun" but the two others still remain in the field. How do I solve this problem?
View 3 Replies
View Related
Aug 2, 2006
I have a form which has a subform on it and this performs a query and displays a list of courses only done by a particualr provider which changes when you change the record i now have buttons beside each course and want to be able to get it to open up another form and then display dates for that particualr course and whos attending whats the easiest and best way to do this as im really stuck and need to get this finished.
View 4 Replies
View Related
Dec 7, 2005
Hello All,
I'm only fairly basic when it comes to Access, and I need some help with a query. Im running Access 2003 on XP Pro, and my current database is in a one to many relationship with Staff and Activities. A part of the Activites field involves the expenses of that particular activity. I have a field in my Activities table saying total expenses. The expense formats are in currency (including total). When I go to make a query, I'm using the following critera for the TotalExpenses field. "FoodExpenses"+"OtherExpenses". However, when I go to run the query, it tells me that there is a data type mismatch in the criteron expression. I changed the field formats to text and it will run. Can access 2003 support currency based querys and if so how? It would be really handy to know.
View 5 Replies
View Related
Jun 19, 2006
I have to do a query that uses as condition a row from a switchbord.... Is that possible?? Can u help. (I created my switch bord with the Access tool).
Thanks.:cool:
View 2 Replies
View Related
Sep 7, 2006
Hi guys
The question i am looking for help with is.....
i have a database with a form(form1) and a subform(sub1), when i query this it will only desplay information that has all boxes filled in. i want it so that if there is info in form 1 but no info in sub1 i will still display the info from form 1. At the moment it just ignores the whole thing and does not give certain records from form 1.
any ideas?
James
View 2 Replies
View Related
Apr 24, 2007
Hey, I have a database and a login panel and I need to stop some users from using ctrl + c in query's or through out the database. The way I have set the db up is if a user logs in correct then they get directed to the appropriate switchboard. I have already disable all toolbars therefore disabling right click and on my forms I have vba to stop ctrl + c but can't figure out how to do it on queries, I have tried search and searching Google but if I have missed a post please include a link, any help would be appreciated THANKS :)
View 3 Replies
View Related
Nov 2, 2007
Is there a tool that converts Access querys (jet syntax) to sql views and procedures (sql server syntx)?
Thanks
View 3 Replies
View Related
Feb 4, 2008
Hi, I have done a search before posting but cannot find what I am looking for.
At work I use a database to record incidents (work that I do). I have written a query for incidents for 2007 and 2008 and I can do a line graph for each year showing the monthly incidents by doing Insert, Form, Chart Wizard
What I am trying to do is a line graph for 2007 and 2008 together to compare performance but i can only ever choose one query for my graph, either 2007 or 2008.
Is there a way I can get both years on one graph so as i can compare performance.
Any help/guidance appreciated.
View 9 Replies
View Related
Jun 2, 2005
Hello,
I have about 1500 records in a table. We use a form to search through the records to find info on certain accounts. We now want to only search through accounts that are current, so I made a query to see only current accounts. I switched the search form from going off data right from the table to the new current date query. Now when you load the form it says "running query" and the application stops responding. The query seems to load up quick in raw format.. how come the application freezes when searching using the query instead??
thanks,
Matt
View 1 Replies
View Related
Mar 6, 2006
i'm struggling here
i have a simple cross tab query. i'd like to put a parameter value as criteria. lets say i hard code the criteria with 2006 it works fine. but when i reference it to the value in a form e.g forms!frm_navmaster!txtyeardate then i start having problems. i don't know to start. i've tried using the expression and where values in design view....uuggghhh.
any ideas are appreciated
View 3 Replies
View Related
Jan 15, 2008
Hi to all
I have a .mdb Files. If i doubleclick this file the database opened but i can't see the tables and Querys. It's a program that start automatically.
I can only see the source code if i enter Alt + F11.
What i must do for see the Querys and the Tables?
regards
cobra
View 4 Replies
View Related
Aug 1, 2014
I have been working on a simple data base for some time now (beginner level) and am still trying to improve it. I would like to do something but before that I would like to have your opinion to know if it is even possible?I have a query QryMainReport:
Start Date/Time
End Date/Time
Employee
At the moment this is what the format of my report looks like (I removed other unnecessary fields):
StartTime----------EndTime---------------Employee
12/06/2014 01:00--12/06/2014 03:00------John Smith
12/06/2014 04:00--12/06/2014 06:00------Jane Doe
13/06/2014 02:00--13/06/2014 05:00------John Smith
13/06/2014 08:00--13/06/2014 08:00------Jane Doe
I would like to do as a report. (Dates would always be from Sunday to Saturday). I am not sure it is possible to do that. I suppose first it would mean:I would have to do a query to separate the times from the dates?I would have to find a way for Access to find the unique dates and unique names?Does it mean I have to use cross tab queries?
View 2 Replies
View Related
May 23, 2005
A customer's name is SZEE. Seek him through the SName textbox with Find, and Access can't find him. (Same in the table.) Seek him with a wildcard Sz* and there he is.
I've tried it on another machine - also with Access2k - and it's the same.
Is it an Access quirk? Is there an answer? (The client asks why. Be good to be able to say.) Cheers.
View 3 Replies
View Related
Mar 16, 2006
I have created a report from the results of a query. The query has 2 parameters.
SELECT Nonconformances.DateRaised, Nonconformances.Customer
FROM Nonconformances
WHERE (((Nonconformances.DateRaised) Between [Enter start date] And [Enter end date])) OR (((Nonconformances.Customer) Like [Enter customer name] & "*"));
Now the problem is that I have been asked to add the search criteria to the report header. If the search criteria came from a form, no problem, but the user enters the criteria into a parameter box generated by the query. So if the start date was 1/5/05 and end date was 31/12/05 and the customer search was F.C, how do I capture this and show it on the report?
Any help appreciated.
View 10 Replies
View Related
Sep 1, 2006
Hi
When I want to run a form it prompts me to enter some parameters. I wonder what could have caused this issue?
Any help will be very much appreciated.
B
View 3 Replies
View Related
Jun 3, 2005
I have a date field. I have
Between [Please enter second begin date] And [Please enter second end date]
as my criteria so that the user may enter two dates. But, I want the extreme dates to be included in my data.
For Example: If the user enters 1/1/2005 and 1/31/2005, I want the information for the 1st and 31st to be included. How can I do this using just about the same criteria statement?
View 8 Replies
View Related
Nov 29, 2005
Below is a query in SQL view that is driving me crazy.
When ran it ask for a StartDate, EndDate, StartDate, EndDate.
Can someone please look and see if they can determin where the criteria is coming in from. In design view there is no criteria set up to ask for dates. Also, there is no parameter set in the parameters box.
SELECT tblBooksAndContracts.intTrackingNumber, tblGroupInformation.strGroupName, tblGroupInformation.strGroupNumber, tblSystems.ysnSystemWork, tblSystems.dtmSystemWorkComplete, tblGroupInformation.dtmDateMembershipReceived, tblGroupInformation.strRegion, tblBooksAndContracts.ysnNeedBook, tblBooksAndContracts.ysnNeedContract, tblBooksAndContracts.intBookAndContractCarveOut, CompareDates([tblBooksAndContracts].[dtmBookShipped],[tblBooksAndContracts].[dtmASODraftsSent]) AS [Book Complete], tblBooksAndContracts.dtmContractDistributedToMarke ting AS [Contract Complete], CompareDates([tblBooksAndContracts].[dtmBookShipped],[tblBooksAndContracts].[dtmASODraftsSent],[tblBooksAndContracts].[dtmContractDistributedToMarketing]) AS [B/C Complete], ([tblGroupInformation.dtmDateMembershipReceived]+[tblBooksAndContracts].[intBookAndContractCarveOut]+30) AS ECD, CompareDates([tblBooksAndContracts].[dtmBookShipped],[tblBooksAndContracts].[dtmASODraftsSent],[tblBooksAndContracts].[dtmContractDistributedToMarketing],[tblSystems].[dtmSystemWorkComplete],[tblGroupInformation].[dtmDateMembershipReceived]) AS [Master Complete Date], tblGroupInformation.dtmEffectiveDate, tblGroupInformation.strRegion, tblGroupInformation.strNRC
FROM (tblBooksAndContracts INNER JOIN tblSystems ON tblBooksAndContracts.intTrackingNumber = tblSystems.intTrackingNumber) INNER JOIN tblGroupInformation ON (tblSystems.intTrackingNumber = tblGroupInformation.intTrackingNumber) AND (tblBooksAndContracts.intTrackingNumber = tblGroupInformation.intTrackingNumber)
WHERE (((EntryIsComplete([ysnSystemWork],[dtmSystemWorkComplete],[ysnNeedIDCard],[dtmMailIDCards],[ysnNeedBook],[ysnNeedContract],[ysnNeedDraft],[ysnNeedFlyer],[ysnBookAndContractComplete]))=Yes) AND ((IsBetween([startDate],[endDate],[tblBooksAndContracts].[dtmBookShipped],[tblBooksAndContracts].[dtmASODraftsSent],[tblBooksAndContracts].[dtmContractDistributedToMarketing],[tblSystems].[dtmSystemWorkComplete],[dtmMailIDCards],[tblGroupInformation].[dtmDateMembershipReceived]))=Yes));
As well as when it does run, and you enter the span dates, if the field is blank it inputs 12:00 am in the field and includes it in the query. I checked the tables and it is not set up to input 12:00 am as a default value, nor is it stored as 12:00 am in the tables.
I am at my wits end here and any help/advice would be helpful.
If this does't make sense let me know and I will try and explain further.
Thanks in advance!
*please disregard the grammer, I am typing this fast before I head out to get the kids..a mommies job is never done!*:eek:
View 10 Replies
View Related
Sep 21, 2006
Please look at this sql statement and tell me where the error is. When I try to open the recordset, I get a "Too few parameters. Expected 1" error. That kind of error usually goes with missing # on dates or ' on text
DateTime is a DateTime field that defaults to Now().
The problem appears to be in the dates because when I comment out all after the user parameter, it works, and taking out the "AND Used Is Null" doesn't help.
sqlstr = "SELECT * " & _
"FROM tbl_Usage " & _
"WHERE User = '" & UsrNm & "' " & _
"AND DateTime >= #" & Date & "# and DateTime < #" & DateAdd("d", Date, 1) & "# AND Used Is Null"
This is from the immediate window
?sqlstr
SELECT * FROM tbl_Usage WHERE User = 's5ucba' AND DateTime >= #9/21/2006# and DateTime < #9/22/2006# AND Used Is Null
View 1 Replies
View Related
Sep 4, 2007
Hi
I am using 'Like "*" & [Forms]![Template]![Combo433] & "*"' in a query to a combo box on a form so if I have nothing selected in the combo all data is shown, except this does not show any null values!
Can any advise on how I can adapt this to allow for null values!
Thanks in advance...
View 1 Replies
View Related
Dec 25, 2007
I have designed an access database for an organisation. The database is basically all the people who work for an organisation, their addresses, the borough they work in and other details. I have deisgned a query that picks out all the people who work in a particular borough, by using parameters. So when I click a button, a box comes up and asks me to put in the borough and voila, I get a list of all the people who work in that borough. But what do I put in the parameter box, if I want the entire list of people in all the boroughs not just one particular borough?
Really appreciate the help...
View 1 Replies
View Related
Jun 13, 2005
I have a form that lets the user enter a beginning date and ending date in text boxes, then below that asks the user to choose a major category from a combo box. That works fine. The user the hits a command button below which opens a report. On the report is a chart based on the information they provide. This works well too. Also, the heading of my report contains the parameters in which the user gave to develop the report. The dates come up fine because I used a text box and put in =[Forms]![myformname]![parametername].
But this code does not work for a combo box. It will not pick up the choice that the user makes. Does anyone know how I can capture the answer so that I may display it in my report?
View 1 Replies
View Related
Feb 15, 2007
How would I go about creating a parameter that can search for all events that occurred in a particular month. I don't want the user to have know the exact date, just the month and year.
View 1 Replies
View Related
Nov 8, 2007
I have a report thats based on a table works well(thanks to the help from you guys) my problem is..When the parameter comes up to put in the customer name I would like to change that into a list box for spelling reasons, I have changed "customers" in all tables and quereies to look up list box but my parameter only comes up with a part for me to type the customer name.can this be changed? if so how please and I am a novice at this so please try and keep the help as straight forward as possible
cheers
View 5 Replies
View Related
May 3, 2006
Hi,
I have a Data Access Page which is generated on behalf of a query.
Query uses one parameter field ,so when is started it promts with the form named Enter Parameters in which I have to input parameter value.
So before the DAP is opened, first is displayed parameter form.
I would like to change dimensions of that form(height, width) but I dont know how.
I already tryied by opening DAP in design view & then in microsoft script editor but i couldn't find anything about this pop-up parameter form.
Any suggestions are wellcome:rolleyes:
View 1 Replies
View Related
Jul 26, 2005
I am trying to strengthen an already developed database at my work. They had a form with five different text boxes, each one that would run a seperate query on the same table, and the results would open on a different page. I am trying to combine all these text search parameters from the form to a single query and have the result come up on the form. I have done every search I can thing of from a forum search, a google search, and just reading through query forum posts for about a day; but I still cannot tell what is wrong. If someone could look at my code, and see if I have a mistake, or if I'm even in the right direction. :confused:
SELECT FLIGHTS.[FINISH OD], FLIGHTS.[PIPE OD/SHAFT OD], FLIGHTS.PITCH, FLIGHTS.[TYPE OF MATERIAL], FLIGHTS.[BURNED OD], FLIGHTS.[BURNED ID], FLIGHTS.[RIGHT/LEFT HAND], FLIGHTS.PartNumber, FLIGHTS.COMMENTS, FLIGHTS.[START TIME], FLIGHTS.[FINISH TIME], FLIGHTS.PurchaseOrderNumber, FLIGHTS.QTY
FROM FLIGHTS
WHERE ((FLIGHTS.[FINISH OD]=Forms![KNOWN BURN SIZES]!Text25 & "*") And (FLIGHTS.PITCH=Forms![KNOWN BURN SIZES]!text37 & "*") And (FLIGHTS.[TYPE OF MATERIAL]=Forms![KNOWN BURN SIZES]!Text32 & "*") And (FLIGHTS.PartNumber=Forms![KNOWN BURN SIZES]!Text0 & "*") And (FLIGHTS.PurchaseOrderNumber=Forms![KNOWN BURN SIZES]!text36 & "*"));
The FLIGHTS is the main table, [KNOWN BURN TABLES] is the form that the parameters are entered. I want users to be able to enter any fields they want and leave others blank. Currently all I get is a blank query. Sorry if the answer seems obvious; I tried avoiding posting till I knew that I couldn't figure it out on my own.
Thanks.
-Brian
View 8 Replies
View Related
