I have a table called Coded_Data with one particular field called Codes. This field list a number and a respective code to identify what type of code it is. For instance 123456789-AD...123456789-ADS. I am wanting to create to two columns. One listing all work orders coded with AD and the other withADS. When I use the Like condition with % it returns both AD and ADS in same column. I understand why because the "%AD" returns anything that starts with AD so this is why I get both codes. I only want to get the the AD codes in one column and ADS in the other. I have duplicated the table twice since I am trying to retrieve data from the same field and table but display in 2 separate columns. The duplicated table has a different alias. The problem is in the where clause.
where (((coded_data.codes) LIKE "%AD")) or ((coded_data_1.codes) LIKE "%ADS");
I have created a simple Unbound search form based on a 'Bookings' table. There are 4 comboboxes that the user can choose to search on, 'Client_ID', 'BookingType', 'FundingArea' and 'ChargeTo'. I have created a query that uses the following criteria;
Like "*" & [Forms].[frmLBT_db_Tool].[cmbName] & "*" Like "*" & [Forms].[frmLBT_db_Tool].[cmbBookingType] & "*" Like "*" & [Forms].[frmLBT_db_Tool].[cmbFundingArea] & "*" Like "*" & [Forms].[frmLBT_db_Tool].[cmbChargeTo] & "*"
Using this criteria any of the comboboxes can be left blank and the results will display the chosen combobox used, or if 2 comboboxes were used the results would be further filtered.
The problem I have is that if a name was chosen (cmbName - Client_ID) and the Client_ID was 2, then all Client_ID records which have a 2 in them are returned, 2, 12, 20-29,32,42,52 etc...
Is there a way to overcome this, or am I going about it all wrong?
Is it possible to update vlues in a table using wildcards. I want to use a query to search for certain records that meet a certain criteria and modify these values like changing particular dates in the date field to new values.
I have a table with a string field for a street. It might have data such as:
Bleeker Street or Bleeker St.
I want to create a query that will enable the user to search for a string by entering a parameter. Is there a way to construct a query using wildcards for a string so that if the user types in "Bleeker" both the above values will be returned?
I am trying to copy notes from one table to another table where a condition has to be met and I can't figure out how to do that in an APPEND query.
Each record has a unique number that comes into my Initial Table in my Access database from a construction program I download. In this particular table both the [DNJTNo] and [DNVersionNmbr] fields can have duplicate numbers. However, when I run the query I want it to add a record to the second table only after the query checks that the [DNJTNo] in combination with the [DNVersionNmbr], is not already in the table. If it is, I do NOT want it appended to the second table. (I have attached a picture of the query in APPEND design stage).
I have a table that has 4 fields; id_vl, id_product, date_vl, vl.
Basically each product is valued either on a monthly, weekly or daily basis. I want to create a filter that is based on a text box that filters the dates from which one can look at the dates of valuation and the associated values.
The problem comes with the products valued on a weekly or monthly basis. For example, if I simply say [tblvl].[date_vl]>=Me.textbox, and me.textbox = 13/09/2013, yet the last value date for a weekly product was 09/09/2013, this record won't be included. Therefore I need to set the date of the previous record as the filter. This is my attempt but it doesnt work...
FROM tblVL WHERE iif(tblvl.date_vl<>me.fees_start_date, [tblVL].Date_VL>=(SELECT MAX(PrevDate.Date_VL) FROM tblVL AS PrevDate WHERE PrevDate.Date_VL<[tblVL].[Date_VL] AND PrevDate.Id_Product=tblVL.Id_Product;),tblvl.date_vl>=Me.fees_start_date) ;
I have a sql to run and I have to use many values as parameters (over 1000), which is why I am trying to use a SQL inside a IN condition.
I am testing the sql with only 1 value for now. The parameter I am using will be 2208287. This returns in a matter of seconds.
Code: Select a.po_id, a.sku12, sum(a.unit) AS Units, sum(a.weighted) AS Weighted_Units, ((sum(a.weighted))/ (sum(a.unit))) AS weighted_turntime From( SELECT ds.src_evnt_txn_tmst AS alloc_ts, bc.src_evnt_txn_tmst AS ship_ts, ds.Po_id, Left(bc.sku16,12) AS SKU12, ds.distro_id AS alloc_distro, bc.distro_id AS Ship_distro, Sum(bc.unit_qty) AS Unit,
[Code] .....
but it does not work when I add a sql inside the IN condition. It actually runs forever and eventually I have to break it to stop. why this is not working with a sql inside a IN condition.
Also note:
table ROSS_REPORT_DISTRO_DTLS_SUM is a linked table table BOL_CONTNR_SKU16_RTED is a linked table table CopyOfdata is a local table (This is where I will put all of my values to act as parameters)
Code:
Select a.po_id, a.sku12, sum(a.unit) AS Units, sum(a.weighted) AS Weighted_Units, ((sum(a.weighted))/ (sum(a.unit))) AS weighted_turntime From( SELECT ds.src_evnt_txn_tmst AS alloc_ts, bc.src_evnt_txn_tmst AS ship_ts, ds.Po_id, Left(bc.sku16,12) AS SKU12, ds.distro_id AS alloc_distro, bc.distro_id
I have a POLEFFDATE field with dates in date/time format. A single record has POLEFFDATE equal to 12/15/2013. I calculated a simple field called EVALUATE as follows: IIF([POLEFFDATE]< 1/1/2014,1,0). EVALUATE should equal 1 for this record, but it equals 0. Why ?
I have to three fields in my query wizard, lets call them A and B and C. The condition for the query is either A or B is Null and C is Null. Im not sure how to do this though becuase A, B and C are different fields. I wrote
Code: Is Null
.
under the criteria of A and B and C, but this specifies that A,b and C is Null, how can I make it so that C must be Null and either A or B or Both.
so I want to find all the employees in tblEmployees whose EmployeeID may or may not be in tblMailsSent with some other MailID's but definitely not with the specific one I am looking for.
I find my own method for doing this rather clumsy, so do you happen to have a nice recipe?
I want to update a table called tblFinalOrder, that looks like this:
In particular, I want to update each column separately with the number 1 taken from table tblSAP_XWP_SW:
My problem is that Access doesn't properly update the table how I want. I join both tables using an INNER JOIN on the SAP number. In the column AEMenge you see some 1's. So what he should do is writing these 1's into the appropriate column in my tblFinalOrder table. The condition is that the SAP number must already be in my tblSAP_XWP_SW table. If he doesn't find a 1, then skip it.
Here is my code so far. This one should update column "DynaCT". The funny thing is that DynaCT isn't available in my columnlabel column but he writes a 1 anyways (or in this case anohter number, I was playing around with it). This is what I don't understand.
Code: UPDATE tblFinalOrder AS a INNER JOIN tblSAP_XWP_SW AS b ON a.SAPNr = b.sapxwpsw_sapnr SET a.DynaCT = 1 WHERE a.SAPNR IN (SELECT sapxwpsw_sapnr FROM tblSAP_XWP_SW);
I mean, he should only write the 1 into the specific column of table tblFinalOrder, if he finds the SAP number in tblSAP_XWP_SW and if there is a 1 in this line in column AEMenge.
condition formatting a date field in access.The query used for this field produces a date or "NA" based on the formula below. so when the date shows up as "NA" then i have set the condition formatting of the cell to grey and this works works well. but when it shows up with a date it doesnt format to a grey.the formatting pane has these two expressions
1. Q_Induction_Date = "NA" then grey the cell
2. Q_Induction_Date < DATE() then grey the cell and this DOESNT work. i have also tried the function Now() and that doesnt work either.
I want to list all the records which contains data as per my filter form field company.
I have tried this
Like IIf(IsNull([Forms]![Filter]![COMPANY]),"*","*" & [Forms]![Filter]![COMPANY] & "*")
problem is this i want to display all the records including null, but this code only show all records except null
Like IIf(IsNull([Forms]![Filter]![COMPANY]),"*" here we have to add codes for null values also but i don't know how ,"*" & [Forms]![Filter]![COMPANY] & "*")
hi guys.. i want to include a search form in my database.. here is my current SQL statement:
SELECT tblCompany.CompanyIDPK, tblCompany.CompanyName, tblCompany.CompanyAddress, tblCompany.ContactPerson, tblCompany.ContactPosition, tblCompany.OfficeNo, tblCompany.MobileNo, tblCompany.FaxNo, tblCompany.EmailAdd FROM tblCompany WHERE (((tblCompany.CompanyName)=[Forms]![frmSearchClient]![txtSearchFor]));
but this statement only returns the exact name of the company... if the name of the company in the database is ABC company and you searched for "ABC", it will return zero results... so how can i improve on this? that even if they enter only "ABC", it will return a result.
I'm making a student report card. Teachers enter student data via a bound form. I have a text field that teachers enter their class which brings up student names. They fill oput the form accordingly.
I have 6 classes of grade 7. 7A, 7B, 7C and so on.
If a teacher enters something weird like (7 (space) A) the query bound to the form will return nill results because it's looking for something that doesn't exist. I need an error message to appear which says no records will be returned because you haven't entered the correct parameter value. (Because they have entered 7spaceA rather than just 7A.
I could just use a combo and this would solve all my problems. But it's not always practical. I was thinking I could use a wild card where if the first character was a number then at least some values would be returned. There is method to my madness... I actually want to apply the same code elsewhere when running reports.
Is there a way I can enforce some kind of input mask in an event proceedure that can do this??
I have data of type "number" shown in the drop-down combo-box in a form. It has some codes in the event of "After Update".
My problem is that i can't type in something like 13* in the combo-box in order to get anything starts from 13. It says "The value you entered isn't valid".
The reason is that i want user to be able to use the wildcard. I know that i can do this with text. I'm not allowed to change the structure of the table in the database.
This combo-box shows IDs from one of the exisiting tables. That IDs is of type "autonumber" in the table.
I've got a query that is defeating me, but that might be due to a week's worth of insomnia related sleep problems. My issue exists in the following query, of the four IIF statments in the HAVING portion of the query and the need to select all if the statement is false for each one of the four queries, the way it is now partly works by returning all results where none of the values are null, I need to get null results as well. I've tried not including the false action for them, a wildcard to select all and just can't get around this problem. Any help appreciated.
SELECT DISTINCT vw_mdb_SalesSummary.Channel, vw_mdb_SalesSummary.Store_Number, [AOI Category groups].ProductType AS Category, dbo_Contact.Source_of_Inquiry, dbo_Company.Rn_Descriptor AS Store_Name, vw_mdb_SalesSummary.Job_Number, Sum(vw_mdb_SalesSummary.Extended_Price) AS Sales1, vw_mdb_SalesSummary.Designer_Code, vw_mdb_SalesSummary.Manager_Code, 0 AS Sales2
FROM ((dbo_Company RIGHT JOIN vw_mdb_SalesSummary ON dbo_Company.Store_Number = vw_mdb_SalesSummary.Store_Number) LEFT JOIN dbo_Contact ON vw_mdb_SalesSummary.Job_Number = dbo_Contact.Job_Number) LEFT JOIN [AOI Category groups] ON vw_mdb_SalesSummary.Category = [AOI Category groups].SalesCategory
WHERE (((vw_mdb_SalesSummary.Order_Date) Between [Forms]![frmLandS]![currentStart] And [Forms]![frmLandS]![currentEnd]))
GROUP BY vw_mdb_SalesSummary.Channel, vw_mdb_SalesSummary.Store_Number, [AOI Category groups].ProductType, dbo_Contact.Source_of_Inquiry, dbo_Company.Rn_Descriptor, vw_mdb_SalesSummary.Job_Number, vw_mdb_SalesSummary.Designer_Code, vw_mdb_SalesSummary.Manager_Code, 0
HAVING (((vw_mdb_SalesSummary.Channel)=IIf([Forms]![frmLandS]![Channel]<>"",[Forms]![frmLandS]![Channel],[Channel])) AND ((vw_mdb_SalesSummary.Store_Number)=IIf([Forms]![frmLandS]![Store]<>"",[Forms]![frmLandS]![Store],[vw_mdb_SalesSummary]![Store_Number])) AND (([AOI Category groups].ProductType)=IIf([Forms]![frmLandS]![AOI]<>"",[Forms]![frmLandS]![AOI],[ProductType])) AND ((dbo_Contact.Source_of_Inquiry)=IIf([Forms]![frmLandS]![source]<>"",[Forms]![frmLandS]![source],[Source_of_Inquiry])));
I am trying to get the OrderBy to recognize wildcards (or to that effect). A scenario would be I would have more than one name in a field, EX: Name2, 4, 6. How do I use wildcards in the OrderBy and they are recognized.
ORDER BY (Table.Field)="Name1", (Table.Field)="Name2", (Table.Field)="Name3", (Table.Field)="Name4", (Table.Field)="Name5", (Table.Field)="Name6", (Table.Field)="Name7", (Table.Field)="Name8";
Im trying to create a query that uses a combo box as the filter criteria. However the combo box needs to use wildcards, which i would have thought would have been *[forms]![control]![combo1]*. However this doesnt seem to work, anyone know what im missing?
I'd be grateful if someone could offer some help with this to a frustrated Access novice : )
I have a query that filters records according to the value of an unbound text box in my form (basically search on last name)
I want to introduce wildcards into the filtering process but cannot get it to work.
Here's what I've done:
In the criteria field I had [Forms]![MAIN]![Text440] refering to the unbound text box on the form already mentioned.
This worked fine, but the following: Like "*[Forms]![MAIN]![Text440]*" does not.
Nor does various combinations of brackets such as: Like "*([Forms]![MAIN]![Text440])*"
In fact it now doesn't return anything, even if given a positive match. I have trawled help and the forums but to no avail. I have also tried the % character instead of *
