Im trying to concatenate a bunch of fields (50 arghh) which each are either blank or just contain one letter. This was someone elses setup for an attendance register which I think is an odd way of doing it. I would have used one field and then to get the mark for a week take a substring at the appropriate position. Anyway im trying to replace an empty string field with a letter to represent the register hasn't been marked using sql statement:
SELECT [400 Student Marks].acad_period, [400 Student Marks].student_id, [400 Student Marks].register_id, [400 Student Marks].register_group, Replace([1],"","U") AS attendance FROM [400 Student Marks];
[1] being the first register week then I would have concatenated with [2] etc...
This however causes an error each time on the attendance field so im guessing replace doesn't work on empty strings. Is there a way round this/alternative.
strSQL1 = SELECT FieldA FROM table WHERE FieldA = xxx strSQL2 = SELECT FieldB FROM table WHERE FieldB = xxx
I need unit this two strings to third string strSQL3. I must have result StrSQL3 = SELECT FieldA, FieldB FROM table WHERE FieldA = xxx and FieldB = xxx
Lets say those are my values. Well in the formula >"5". 10 Will not show up correctly. Does anybody have a piece of code or something that will correctly display 10 as >"5". Any help greatly appreciated. Thanks
I am normalizing data from a spreadsheet of just over 4000 records. The spreadsheet has a "Model" field that contains both the model number and a model description, separated by a space. I would like to split this field into two different fields: "Model_Number" and "Model_Description". The model number is one word of varying lenths.
Hello, I would like to put a restriction on the SQL statement such that if one particular field contains the character "MO", then we take in the record, else we ignore them. May I know how could this be done in the "WHERE" part of the SQL statement? I do not think SQL would read things like Instr(). Thanks!
I have a loop where i use a string (strCount). The first time I run the loop strCount has de value A. The second time strCount must become B, the third time C, etc
I already tried to strCount = strCount + 1 but it did not work does someone know how to do this?
This is a database that was handed off to me for upgrading.
The person who initially created it made the Name field in the table contain the entire name... So a single entry in the field looks like
CHRIS R LOUNSBURY
The table is in the attached table. This is merely a sampling. The main table has over 3,000 names in it. Is there an easy (or maybe not so easy) way to automatically parse that data and split the names into their own fields.
An example would be the string CHRIS R LOUNSBURY. Run code which takes the string left to right to the first blank space, and splits it off into First Name field. Take the last part of the string (right to left) to the first white space and put it in the Last Name field.
Is this possible? Or do I need to find myself a temp employee to data entry all this over again
I have a Row source string which I use quite often. Currently, I declare it in each procedure and copy and paste it in. The problem is when it needs changing, I need to change it in a billion places.
can I declare the string as public and set it as public too so that I can just make a reference to it rather than copy and paste entire slabs of code into each event??
Access does not have an option to sort alphanumeric strings properly with the result that sorting a column with the following (sort of) data is almost impossible.
I need to be able to get addresses sorted correctly and they always have a mixture of alphabetic and numeric characters.
The steps I have followed to try and achieve this are as follows:
Create a matchfield containing the data for sorting (typically the street number followed by the flat number/name)
Split the matchfield into separate fields where there are spaces. "Room A13" becomes "Room", "A13"
Then split the fields/columns by separating the alphabetic and numeric portions - i.e. "Room A13" becomes "Room", "A", "13". This I have not been able to achieve successfully.
(Thinking about it the first step of separating by spaces is probably not necessary. All that is needed is to separate the numeric and alphabetic data)).
Once you have separated the data into alaphbetic and numeric content a sort is straightforward.
Why can't Access cope with what I would regard as a fairly basic requirement (i.e. to be able to sort alphanumeric strings correctly).
Does anyone have a suggestion how I can solve this problem until Access is improved?
i am importing a tab seperated file into a table, and one of the fields is a user comment.
all is well -- until a record is imported that contains comment with a carriage return followed by some more text. the text after the carriage return is imported as the next record.
if the file is tab seperated, why is the carriage return causing the import to act in this way?
I'm confused. I developed a subset of data in the same general format as our main database. When running select and update queries on this subset, I am able to use Left, Right and Len expressions such as "Right([Year],2)" and "Right([Latitude],Len([Latitude])-3) to update and generate new fields of edited data. However when I use the same expressions on the same tables and fields in the main database, I get a reply such as Function is not available in expressions in query expression 'Right([Year],2)'. A coworker says he has encountered the same problem. Has anyone else encountered this and found an interpretation/solution????
I'm trying to parse text strings by certain spaces and characters within the string. For Example, if my text string is RAST 2006-A1 B1 mtge, how can I 1) create a new field with just the RAST 2006-A1, 2) Create a field with just the RAST 2006-A1 B1?
Does anyone know how to create a query object using a string that contains SQL code?
The reason I ask is that I want to be able to run an ad hoc query, but I can't use a RecordSet because there's no way (that I know of) to take the results of a recordset and create a query display out of it (meaning, make it look like a query was executed)
So what I really want to do is to create a query, and then execute it with "DoCmd.OpenQuery()" If that's possible. Any ideas?
I have supplier records where the supplier name in each record is different. It may be Supplier ABC, AB,C, ABC!, ABC Inc., or several other variations. In addition each of these suppliers has their own supplier ID. The supplier ID can be one of 5 different versions since the data is from 5 different ERP systems. I am trying to create one table that does not have the supplier listed many times, but has one correct name and one ID that will be able to link back to the original file with all the different names and IDs. I hope this is a good explananation. Any advise?
Hi I am trying to read a large file and to check if the phone number field is valid. If there are more than 6 "1"s, "9"s or "0"s in the field, then I want to replace it with a null otherwise, I will treat it as a valid number. Can you help? Noel
