does anyone know how to append a string to an existing text field so i have a record and the value of this text field is say "abc" i run an update query which pulls the string "def" out of another table and then now the value is "abcdef"
I have tried the following sql
UPDATE TBL_TmpSubmission LEFT JOIN TBL_PropertyType ON TBL_TmpSubmission.PropertyType = TBL_PropertyType.PropertyType SET TBL_TmpSubmission.ErrorCode = [TBL_TmpSubmission].[ErrorCode] & "property" WHERE (((TBL_PropertyType.PropertyType) Is Null));
which I expected to append the string "property" to the field which already had the value "measure" but it just overwrites the string having the same effect as
UPDATE TBL_TmpSubmission LEFT JOIN TBL_PropertyType ON TBL_TmpSubmission.PropertyType = TBL_PropertyType.PropertyType SET TBL_TmpSubmission.ErrorCode = "property" WHERE (((TBL_PropertyType.PropertyType) Is Null));
i have a temporary table in my db which is checked for errors, that is to say there are three fields in a further lookup table and three of the fields in this temporary table TBL_TmpSubmission are checked for nonexistent property types, fuel types and measure types by queries with the structure given above
it all has the effect i need of filling in this error code field so the error types can be displayed using a later select query - except when there is say a measure error AND a property error because then one query just overwrites the effect of the other
what i want is to be able to use say letter codes "P", "F", "M", append them to one another and then in my later select display that code so the user can see all the fields which need to be corrected
I have a query that I referenced it to a combo box in a form. In my form I created a combo box which has a list of VendorName and I want my query to opens a VendorName report based on whatever chooses in the combo box so that I use the combo box name in the criteria of the VendorName in my query. Somehow I get an error which says "The Microsoft Office Access database does not organize "[Forms]![Form1]![Vendor_Name]" as a valid field name or expression".
I created a query that pulls information from 2 other queries and everything was going fine until I saved the query. I now get a circular reference error
SELECT [CashValue Link Query1].Facility, [CashValue Link Query1].[Financial Class], [CashValue Link Query1].Date, [CashValue Link Query1].Date, [CashValue Link Query1].[Total A/R], [CashValue Link Query1].Current,
Within a query, I'd like to reference another query field based on a date specified as a parameter.
In my query, there are fields for each month: [January],[February], etc.
I have a field titled [Current Month], based on the parameter [As Of Date]. So if when running the query, the parameter pops up and I type 5/6/2013, it knows that the month is May. I know how to return May in the current month field (format([As Of Date],"MMMM"). But how to I return the value that is in the May column?
I have a query which gets information from 2x tables where the I'd on one table is the reference number on the second table.I would like to know how I can remove the duplicates on my reference number field?
I have a table I'm trying to query information out of. Key fields are below:
RecNo (Key) ParentRecNo Description
I need to have a "record" in the query show both the description of itself, as well as it's parent. I was hoping to use Dlookup directly in the query design. If there is no other way, I suppose I could create a temp table and look through the recordset in VBA and dump them into the temp table, but like I said; quick and dirty was the hope here, it's for my own use, and the tables aren't large 50-100 records is what I'll be pulling out when I use this.
Basically what I have is a database for tracking/logging parcels that arrive to the office. I want to be able to generate a reference number based on the date of arrival: i.e. the reference number should be ddmmyy### where ### is a sequential number. I know that I could just use the primary key's autonumber, for the sequential number but if I do this then the sequence will not restart at 1 on each date and because we receive a lot of parcels the reference number will grow to be too big to print out on the collection slips in just a few months.
two tables (one with the date and staff on duty that day and the other with the parcel's info') with a one to many relationship
I also have a query (Named: FullLog) that picks up the following data from the tables:
The field named Count is a DCount function that I used to find out the number of times each date is repeated. This is the Expression that I used:
Count: DCount("*","FullLog","DateReceived = " & [DateReceived]) [Note that DateReceived is first converted into a string using CStr()]
This is as far as I have been able to get, I have been looking for weeks for a solution to this problem but I have yet to find one. I don't even know if the DCount function is the correct way of doing it, I did read somewhere that this produces a very slow query.
Effectively what I want to be able to get is something of that resembles the following
I have a text field in a Table and on a Query called "Notes" In that field that has data like below:
[04/02/2015:BD] Project is to be assessed by Solutions Planning [03/27/2015:BD] Project prioritized [03/14/15:BR] Entered to system
Im trying to find a way to pull just the most recent line of text, in this case
[04/02/2015:BD] Project is to be assessed by Solutions Planning
into the field next to "Notes" or wherever - an empty field in the query. I searched around, found some stuff and I was thinking of having the code look at the first "[" and count the length to the next "[" and pull out whats in between. Looks like the bracket causes issues in the module.
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?
I have a field called PropertyID which stores a unique 13 digit number, the 10th digit is always a 3. I want to remove the 3 to leave a 12 digit number.
I've tried running an Update query using Replace([PropertyID],"3","",10,1) but no joy, what am I doing wrong?
I have a number of global variables set when a user logs into my application which i want to use in an SQL string to record these in a table however im not sure how i go about it i know simply inserting the defined names in the SQL wont work.
Is there a way to define these in global functions instead?
I am trying to extract the first instance of 3 numbers from a string using an update query to update another field in the same table(Master Equipment) titled "Code". the field is called "Equipment". the strings vary in length as well as the location of the 3 numbers needed, but I have examples of all possible locations the 3 number series could be located below. As you will notice, some fields do not contain 3 numbers together, for these I don't want to return a value.
I need to update the Account table using the Conversion table by replacing the first 4 digits in the Account table with the Conversion value. For example, the first record should be updated from 0041980041 to 10000151980041.
Do access VBA implements parameters passed to query strings in all following parameters?I've been working in ASP.NET/Razor C# and this would be an example of how it would be done:
I'm familiar with writing a replace query to replace characters or strings but what I'm trying to do this time is a bit unique to me. I have a string of numbers that will either be 8 or 9 digits in length. The first 1 or 2 digits will be the State code (1-50 hence the discrepancy in number of total digits), then a 2 digit agency code, then the last 5 digits are a producer code. What I need to do is change the 2 digits for the agency part both to 0. So basically characters 6 and 7 if you're counting from the right. To me it feels like I'm trying to do it backwards so I'm having a hard time writing it.
I have a text column with hexadecimal strings like "4E1B0A65FE3299FE", ie. of length 16.I'm doing an ALTER COLUMN query to convert the column to varbinary, but Access is doing conversion by ASCII, which results in a varbinary field of size 32, I think this is because the text column is in Unicode.
SQL Server has something called "styles" which are used with CONVERT.where you can choose to convert by two characters at a time 2:1..How do I do the same in an Access query? CONVERT/CAST are no supported.
I have a weight field that I've been populating with the weight and the unit of measure. I want to seperate those into 2 different fields. I've created a new field called UnitofMeasure and now I need to go back and remove all the instances of 'oz' and 'lbs' from the first field.
In my query I want to extract the last 10 characters of a string in a column which represent a date in the format DD.MM.YY and then convert these to a real date format to be available for further processing.
My query looks like this:
SELECT Angebotskopf.[Laufende Nummer], Angebotskopf.Angebotsnummer, Angebotskopf.Angebotsdatum, Angebotskopf.Anfragedatum, Angebotskopf.Kunde, Angebotskopf.Ansprechpartner, Angebotskopf.Telefonnummer, Angebotskopf.Faxnummer, Angebotskopf.Projekt, Angebotskopf.Preis, CONVERT(varchar(10), RIGHT(Angebotskopf.Projekt, 8),104) AS TestAngebot FROM Angebotskopf;
But Access gives an error message "unknown function 'CONVERT'"
The "RIGHT" functions works but the resulting column is not being recognized as a date, it is a only a string and therefore useless for processing of any date related calculation.