Queries :: OR Using Part Of String From Parameter Box
Aug 1, 2014
I have a criteria in a parameter query that asks the user to enter a Subsystem number (such as 4596-666-001).
The source data for my database has Systems (e.g, 4596-666) and each system has several Subsystems associated with it (e.g, 4596-666-001, 4596-666-002, etc). The System and Subsystem numbers are stored in the same column of the source data spreadsheet, and hence the same field in my database.
The source data is structured in such a way that Systems and Subsystems have different information. I would like to set up a query that shows the System information as well as the Subsystem information.
For example, if the user enters 4596-666-001 into the parameter dialog box the query returns all the information for 4596-666-001 and 4596-666 (but NOT any other Subsystem).
As a work around I have set up an OR situation where the user first enters the Subsystem number and then another parameter query dialog opens and they enter the System number. This works but is not as nice as simply entering the Subsystem number and having all information returned.
Is there a way to set up my query so the when 4596-666-001 is entered an OR is generated using the first 8 characters of the Subsystem number?
View Replies
ADVERTISEMENT
May 28, 2015
I have a manual date format that looks something like:
02/16/2015 09:06:15 AM PST
I would like to truncate the text so that just the date is showing. For all that I have found, I can remove a string from the beginning? any tips on removing a string from the end?
View 6 Replies
View Related
Apr 30, 2014
I've got a query. One field is an expression on which I'm filtering.
A shortened version of the expression is
Expr1: IIf([Status]=Active,"Yes","No")
There are other parts to the formula, hence why I'm not filtering [Status] itself, but stripping them out gives the same problem.I'm filtering on "Yes".What is happening is when I close the Expression builder, it changes "Active" to a parameter, thusly:-
Expr1: IIf([Status]=[Active],"Yes","No")
Status is a text field, it's a query on a single table, there's no field called Active.
View 4 Replies
View Related
Feb 7, 2005
Hi folks,
I have a table with the words "Church of (whatever)" in one of the records. I am attempting to do an update query to remove the "Church of" part but leave the remaining part of the string. I am using a combination of Format and left but am not having any look. Any suggestion are much appreciated.
Thanks
Colin.
View 2 Replies
View Related
Nov 29, 2005
I have a bunch of server names that have names like aaa.bbb, xxx.yyy. All i need from these server names is the name before the first "." so in the first example all I would need is aaa. I've been using this formula in excel MID(A1,1,FIND(".",A1) - 1). This works great.. Is there any way to do this in access?
View 1 Replies
View Related
Sep 10, 2013
I'm trying to find a function in access that could do the following:
ZAB103-3012
ZAF405-HD-0001
Turn those cells into:
AB103
AF405-HD
Pulling everything to the right of the Z in the last step is the easy part but I can't figure out what function would be able to find the last "-" in the cell and pull everything to the left of it.
View 6 Replies
View Related
Apr 26, 2006
I know there are many posts on this but still cannot find what I want ....
I have a string ....
... <surname>bloggs</surname> <fornames>Jane</fornames> etc.etc.
... <surname>williams</surname> <forenames>Jo</fornames> etc.etc.
In a query I know how to:
Find the Start and End Postions as follows:
StartSurnamePos: InStr([string],"<surname>") returns 19
EndSurnamePos: InStr([string],"</surname>") returns 34
I also know that by adding 8 to StartSurnamePos I can get Bloggs but how do I stop it there.
Using left, Right or Mid how do I pull out just
bloggs Jane
Williams Jo in seperate fields?
Thanks in advanced.
View 1 Replies
View Related
Feb 28, 2012
I have a text box and currently this is my control source
="Testing " & [test]/3
test = 1000 so my text box reports:
Testing 333.333333333333
Is there a way to make it into a form like $333.33...Also is there a way to make [test]/3 come out in a money text form? like "Three hundred thirty three dollars and thirty three cents.
View 7 Replies
View Related
Sep 19, 2006
Hi Folks,
I'm sorry to be stupid, here, but I can't get this to work. I have a parameter query that forms the basis for a report. To get the value for the query, I have a form on which the user selects the value they want from a combo box (cboProgram). The values in the combo box include all of the programs used in the file plus a value called "All" which means - just include all the records. My problem is that when "All" is selected in the combo box, no records are returned.
Things to mention:
"All" has a value of 1 in the lookup table.
I tried the query with the actual values and it works fine. The problem is when I try to do it from the combo box.
My first attempt was to put:
iif([forms]![frmFormName]![cboPrograms]=1, "*", [forms]![frmFormName]![cboPrograms]) in the criteria for that field in the criteria box for lngPrograms.
Access' response was that the criteria value was too complicated to resolve.
So, I noticed that I wasn't saying 'Like "*"' in the first part, but if that's the problem then I can't get the numbers of quotes right. I also tried switching the iif statement around and saying <>1,[forms]..., else some version of 'Like "*", but that didn't work either.
Then I queried around the forum and found a recommendation to make a function to return the value, so I did the following:
In the criteria for the field in the query:
IfAll([forms]![frmFormName]![cboPrograms])
And in a module:
Public Function IfAll(ProgSel)
Select Case ProgSel
Case 1
IfAll = "Like ""*"""
Case Else
IfAll = ProgSel
End Select
End Function
I'm still not getting it. If "All" is selected, value = 1, then no recorsd are returned. Can anyone help me out please?
Gratefully,
-D
View 3 Replies
View Related
Dec 16, 2004
I have what I think is a difficult problem to overcome...
I am designing a form to create an invoice. The user will select a workstream and a date range in form frmInvByHrs. Within this I want two sub-forms, one is frmInvByHrsTsht and the other is frmInvByHrsBill. I want the first one to display all the staff and their hours done, and the second one to be in data entry mode where you can enter the hours you want to bill. Each sub-form is based on a separate query.
Is it possible to do this? ie. to have one sub-form in data entry mode, and the other not? It seems to me that the data entry mode is controlled by the MAIN form regardless of the sub-form settings!
If this is not possible, do you know how I can acheive this?
Thanks
S
View 1 Replies
View Related
Nov 3, 2006
Hi everyone,
I have refined my query from previous threads to involved a module function. This calculates more acurately no of working days between dates and takes into account a holidays table. (All credit to Arvin Meyer on the module:) )
However because the Leave Year starts at the 1 July and finishes 30 Jun I need to compose the date for any current year Year(Now())
Enclosed scrdmp shows my query design. I can easily get it to work as you see it, but obviously as each year rolls over, the year needs to change.
Have looked at many posts but can't find what I'm looking for. This one will get me over the hurdle.
Many thanks,
View 6 Replies
View Related
Feb 11, 2014
I have a sql server query that I need to use in access 2003 but I can not figure out how to convert the "not in" part of the query. Here is the sql server query
Select * from accounts where beg_date between '1/1/2013' and '12/31/2013' and cast(acctNo,integer) in (2,3,4,5,7,12,20) and acct_type not in ('Individual','User','Viewer')
I can not find how to do the "not in" in access sql.
View 7 Replies
View Related
Feb 28, 2015
I have a table - RDC/NDC - that holds details about various warehouses, including a field - [Short_Code] (TEXT) - to hold the warehouse short code or ID.
I have an import table - PickDataImport - that contains details of goods picked, including a field - ToAssignRef (TEXT) - that is made up of 3 elements; Type-Short_Code-Date (EG GREEN-MAN-210215).
I am trying to create a query that will return the warehouse name from RDC/NDC by looking up the depot short code in the ToAssignRef.
The Type element of ToAssignRef can be any length of text.
I am thinking i need to use HAVING or IN (or a combination) but everything i try fails - either blank or errors.
View 10 Replies
View Related
Nov 27, 2013
I need to give the user the ability to select either all of a table say
[tblStores].StoreNo
or only those stores where [tblStores].StoreNo Is Null
I cant for the life of me get my query to accept Null or Is Not Null in the parameter box.
What do I need to do.The end game is to produce a subform to allow users to maintain tblStores
eg
tblStore
StoreNo | StoreNm
000001 | StoreA
<Empty>| StoreB
000003 | StoreC
Null returns
Store B
Is Not Null returns
StoreA
StoreB
StoreC
View 2 Replies
View Related
Jun 26, 2015
I am trying to group records in a query and count them. I have records containing ABC12345
ABC67890
ABE12345
ABE67890
Basically the third letter is what I need to group on so that I can count the number of records with ABC, ABE and any other variant of the third letter but with the numbers all varying all over the place.In the example above I want to find ABC 2 and ABE 2.
View 3 Replies
View Related
Oct 10, 2013
I have a query that will be assisting me find pricing based upon quantity ranges of specific equipment for a given FY.
I'm no access expert, and I keep getting "You tried to execute a query that does not include the specified expression...as part of an aggregate function".
I have tried several things, but cannot seem to figure this one out.
The SQL of my query is as follows:
SELECT IIf(Nz(Sum([Current Orders]![Quantity]))+Nz([forms]![04c Test Query for ROM Support]![Quantity])
Between 1 And 4,[04b Pricing Products]![01-04],IIf(Nz(Sum([Current Orders]![Quantity]))+
Nz([forms]![04c Test Query for ROM Support]![Quantity])
Between 5 And 10,[04b Pricing Products]![05-10],
IIf(Nz(Sum([Current Orders]![Quantity]))+Nz([forms]![04c Test Query for ROM Support]![Quantity])
[Code] .....
View 14 Replies
View Related
Oct 16, 2013
I have to sort out a database of goods where many doublets have been created over time. Unfortunately each doublet/triplet/multiplet can be disguised by not having ANY fields common with the other versions. The only pointer I have is that the SKU of a copy contains the SKU of the original. I have some 6000 SKU's.
I want to identify each SKU that has been copied, and into what SKU or SKUs.
tblSKUs
----------
Seq : sequential numerator for shiwing it on AWF
ID: Id of the item (Autonumber)
SKU: SKU (Text)
Code:
Seq ID sku
1 2205 MQAC2212
2 17416 MQAC2212-black-s-x
3 2258 MQAC2232
4 12815 MQAC2232-red-X
5 2283 MQAC2254
6 13028 MQAC2254-00
7 16278 MQAC2254-red-s-x
8 16298 MQAC2254-red-s-x1
9 16304 MQAC2254-red-s-x2
10 16313 MQAC2254-red-s-x3
11 2302 MQACP
12 12751 MQACP100-X
13 17468 MQBA201-1-s-x
14 17476 MQBA403-1-s-x
15 17216 MQBA503-1-s-x
In the above table, item with Seq 2 is a copy of 1, 4 is a copy of 3, 6-10 are copies of 5, and 12 is copy of 11. Each copied SKU is thus the original SKU + some more characters. Minimum length for an SKU is 4. No maximum length.
I can do anything using VBA, but need to get inspiration to do minimum possible using queries.I would be happy to get a table like this ( for each copy in RH column, the Orginal is listed in the LH)
Code:
Original Copy
MQAC2212 MQAC2212-black-s-x
MQAC2232 MQAC2232-red-X
MQAC2254 MQAC2254-00
MQAC2254 MQAC2254-red-s-x
MQAC2254 MQAC2254-red-s-x1
MQAC2254 MQAC2254-red-s-x2
MQAC2254 MQAC2254-red-s-x3
MQACP MQACP100-X
can I do this with plain queries?
View 6 Replies
View Related
Mar 26, 2014
We have a field that we track paperwork with that is 9 characters. The first 4 characters are a 'julian date', the 5th character is a dash and the last 4 characters are sequential.
Example:
For today, we would use 4085-0001, 4085-0002, 4085-0003, etc.
For yesterday: 4084-0001, 4084-0002, etc.
I have a query set up that will pull records with a julian date of today-1 and today-7 that works. So a result I may get:
4084-0001
4084-0002
4081-0000
4078-0000
4078-0001
4078-0002
I tried formatting the source field with Left -4, but it only shows the first four characters in the result:
4084
4084
4081
4078
4078
4078
How can I count the number of records each day based on only the first four characters? Example:
4084 = 2
4081 = 1
4078 = 3
View 6 Replies
View Related
Sep 7, 2014
I have been given a list of UK postcodes, with the following format L15TG or TS14TGU.
I need to be able to match these postcodes to a list of postcodes I have stored in the database, however, my list are only UK outcodes, so L15TG is just L1 and TS14TGU is just TS14.
So I need to match the records and return the part of the string that matches i.e take L1 from L15TG.
View 5 Replies
View Related
Apr 4, 2013
I have a query with a date field that is formatted mm/dd/yyyy. I have a combo box that is formatted as mm/yyyy. I need the choice from the combo box to be the criteria for the date field in the query. the combo box has to contain the month and year only (which it does now) and the query must return mm/dd/yyyy. I have tried a few statements and the closest I got was a between statement that added 30 days to the combo box selection but that's not really accurate.
View 2 Replies
View Related
May 10, 2013
I have a table of the following structure:
Key 1 Key 2 Key 3 Optional
data1 data2 data3 data4
The 'key' fields are a composite key of primary key fields from other separate tables. I have a form that generates a new 'Key 1' record and I would like to make a query that creates all possible 'Key 2' and 'Key 3' results (new combinations) while key 1 stays the same. How would I go about doing this? I know I can be done.
I cannot really give any real data as I haven't received it yet.
View 4 Replies
View Related
Dec 23, 2014
I have a query result for example = 2.19 days worked. The result of cumulated hours divided by 8 (hours in the workday). What I want to do is take the .19 and multiply it by 8 (hours in the workday) and I would have the result in actual hours which is what I need. Or of course if there is a way to display that into 2 days and 1.52 hours Rounded up to quarters would be even better. But with spending hours looking through forums I didn't find a way to just work with the decimals right of the dot. Trim, format, Len didn't work for me, they all give me funny result.
View 7 Replies
View Related
Feb 25, 2014
I have a query where I want to add sequential counting to all items that are part of a group. I have grouped the query on MaterialCode and want to add a sequencial counter for all orders that have been assigened to a materialcode
For Example:
Key MaterialCode Order Counter
abc1 111111A 987654 1
abc2 111111A 687654 2
abc3 222222B 655511 1
abc4 333333A 251544 1
abc5 333333A 555555 2
View 3 Replies
View Related
Oct 7, 2013
I have a calculated field (Bhours) with the following IIf function:
=IIf(Fields!BDate.Value<=Parameters!Pdate.Value and Fields!EDate.Value>Parameters!Pdate.Value,Fields!H ours.Value,0)
It works perfectly, but I don't like the fact that it has a zero for the false part as I like the field to the blanc instead of filled with a zero.I can't use a space as I need to calculate with this field later on. I tried using "", but then the field displays #error".
View 7 Replies
View Related
May 27, 2014
I've created the following but it keeps coming up with the error message You tried to execute a query that does not include the specified expression 'ICE Team' as part of an aggregate function.
SELECT ztSub.[Master Sheet].[ICE Team], ztSub.[date], Count(ztSub.[Count])
FROM (SELECT [Master Sheet].[ICE Team],[Master Sheet].[Visit Date (planned for)] AS [date],Count([Master Sheet]![Visit Date (planned for)]) AS [Count]
FROM [Master Sheet]
UNION
SELECT [Master Sheet].[ICE Team],[Master Sheet].[Date retasked to?] AS [date], Count ([Master Sheet]![Date retasked to?]) AS [Count]
FROM [Master Sheet] ) AS ztSub
GROUP BY ztSub.[Master Sheet].[ICE Team];
View 5 Replies
View Related
Nov 19, 2013
I need to get an average repair price of multiple part numbers with the same root number i.e.
8 each 8TJ124GGN1
4 each 8TJ124GGM1
7 each 8TJ124GGP1
First I used a query to average the repair price of each subset of numbers, and then queried the query to average all the subset prices together.
If I simply use the AVG function in the first query, I can use it again in the second query to get the average price of all the subset price averages. HOWEVER, if I use the expression:
AdjustedAvgLabor: Int((Sum([LaborPrice])-Max([LaborPrice])- Min([LaborPrice]))/(Count([LaborPrice])-2))
…to get a more accurate average by throwing out the high and low values, I get a:
“…expression too complicated to be evaluated”
when I try to average the averages in the second query using the AVG function.
I don’t know why the expression is “too complicated” since the first query has already completed its computations before the second query begins its AVG function.
View 8 Replies
View Related