I have strings of names in a calculated field named [CurrentDelReverse] that are separated by spaces, but some strings have 2 names and some strings have 7 names.
My problem is trying to grab the second name in the list to show in a new column.
I have tried so many different things and the closest I got to solving it was:
Delegator: Trim(Mid([CurrentDelReverse],InStr(1,[CurrentDelReverse]," ")+1,IIf(InStr(InStr(1,[CurrentDelReverse]," ")+1,[CurrentDelReverse]," ")=0,0,InStr(InStr(1,[CurrentDelReverse]," ")+1,[CurrentDelReverse]," ")-InStr(1,[CurrentDelReverse]," "))))
This returned the second name in the list but ONLY if it had more than two names in the string.
I am combining fields one that has apostrophe's and the other does not. exp "if this field is null use data from this field (which does not have an apostrophe) otherwise use current field (which has apostrophe)
"red" blue orange "yellow"
How can I change that in the query without changing the previous query to
I have a fax number field iwhich has a mask. +(000)-00-000-000 in a table of many records. The problem is that every record has a number in the first part of country code +(001)which must have come there by mistakes, few records have complete and true fax numbers.
I need a query that can Update this Fax_Number field where the length of the string is less than 7 digits. Some thing like UPDATE Contacts SET Contacts.Fax_Number = "" WHERE ((Len("Fax_Number")>"6"));
In a database that holds membership records, I have three years worth of info relating to membership dues paid. I want to extract who has not paid a membership fee for the last three years, so we can strike them off our records, i.e. I have members' personal details, but there are no records in the payments table, nothing has been entered because they just have not paid. Do I run a query looking for a zero length string? Just how do I achieve this? Have I done right in not entering anything?
I am using VBA code to export a small table as a .csv file, which is no problem, except that the customer insists that where a field is blank, the .csv file must contain a pair of double quotes.
In other words, I can produce this: "Field1","Field2",,"Field4","Field5" but they insist on this: "Field1","Field2","","Field4","Field5"
In the table design, I have made the default for each field = "", but I still can't seem to force the double quotes into a blank field.
Heyo, thought I'd toss my line to see if anyone has had a corruption like this:
Today I went digging through some of my tables on a db I've been building and noted that most (but not all) of my text fields had Unicode Compression/Off and Zero-Length-Strings/Allowed. Now, I know for certain that I never touch Unicode Compression and zero-length strings are not my bag in this db so I went to toggle these and I recieved 'Access encountered an error when attempting to save' type error when I, you guessed it, attempted to save.
So I built fresh tables, and imported my data, and all seemed well but digging further through my database I have noted that ALL of my tables seem to be suffering from this in some way.
First off, does anyone know what has happened? Secondly, does anyone have a guess as to why this happened? Lastly does anyone have a suggestion on how to rectify this?
I'm not a huge fan of building this entire gigantic db from the ground up and I'll do it if I need to, but I'd prefer to figure out what went wrong before we get there and it happens again.
I have a continuous form that displays data from a table. What I am trying to accomplish is to change the format of the field if the string length is equal to 11, to "@@@-@@@-@@@@-@". Otherwise if it is greater or less than 11 then no format.
I have tried it using code:
If Len(Me.FormFieldName)=11 Then Me.FormFieldName.Format = "@@@-@@@-@@@@-@" End If
Is it even possible and if so is my syntax correct or am I way off base?
I have a table where I want to break longer strings into three-character strings.
So... for example the data item "Forest," I'd like to generate "FOR" "ORE" "RES" "EST" and write all four to another table, allowing for duplication. Alternately, we could make a list and just count the unique 3-character strings. The end goal here is to measure the frequency of 3-character strings across a set of data.
Code: Select DPTag from tbleqDP UNION ALL select ESGTag from tbleqESG
It is returning all the tags from both tables but putting them all under "DPTag." What I want is two columns in a report. One would have the header "DPTag" and all the values under it would be the tags from tbleqDP and the other would have the header "ESGTag" and all the values under it would be from the table tbleqESG.
What am I doing wrong such that it is returning all the values under the header DPTag?
I have a table that has two columns "Nationality" and "Gender". I wanted to run a query that will Group the nationality and then split the gender column into two columns and after that it will count both gender columns for each nationality. When I posted this question in "Reports" section I got the suggestion to use the SELECT COUNT in SQL. It worked but only for nationality. I couldn't get it work for the Gender column. I searched alot and the only thing I got was the SQL function to split data from one column into two but that also didn't serve the purpose (check the link to see why: [URL]) At last, I went on to create a cross tab query. Selected Nationality as Row header, Gender as Column header and Customer ID as calculation point. And there I got the result I needed. The SQL Code looks like this:
Code: TRANSFORM Count(Register.[Customer ID]) AS [CountOfCustomer ID] SELECT Register.National, Count(Register.[Customer ID]) AS [Total Of Customer ID] FROM Register GROUP BY Register.National PIVOT Register.P_Gender;
I have a problem with my database. When I'm writing in my data to the database via the forms, the data isn't in the correct order when I start the database up again. Its mixed up. I am using 6 tables and combining data. Why?
I have a table with temperature readings over a 12 hour time period. I have to find the average temperature over that time period. I built a query for a specific time period that listed all of the temperatures. I then queried that query to get the average temperature. It works great but there are at least 10 numbers to the right of the decimal place that I don't need. I can't figure out how to minimize the quantity of numbers that are displayed. I tried adjusting the size in the original table but have had no luck.
I thought I had solved my initial problem of removing the apostrophe character for each zip code field. Most of the fields have data that reads '12345-1234'. I want to remove each (') character and the "-1234" so I end up with a zipcode of 12345.
I created 2 queries for this process. I first run a query with the following statement "Left([ZIP],Len([ZIP])-6)". That gets rid of the "-1234' ".
The I run the second query with this statement "Right([ZIP],Len([ZIP])-6)".
These 2 queries work perfectly if the original zipcode is " '12345-1234' ", but if it is " '12345' ", the entire zipcode is deleted.
I have attached 3 (.jpg)'s to show you what I am talking about.
That subject line probably doesn't really explain it !
I am doing some support for a database that has group level security (I can do the support and make the changes they want but I'm not so hot on the security side of it !:o Anyway when I'm in the office that's fine, but when I want to work offline at home I can't open the database because I'm not logging into the server obviously and it seems to be looking for that.
I have the database. I also have the mdw file with the user and groups permissions etc.. How do I separate the database away from this so that I can open it? or how do I open it? I have username and password for admin but I don't think that's any use to me? cos I don't really know how to log on off site...So if there is anything I can do can you please let me know in simple step by step instructions !:confused: :confused:
I have a single field which contains 13 months of budget data separated by ;. See the example below. I'd like to separate the data into 13 individual fields. What function would I use to do that? Thanks in advance.
I have gotten to a point with a database where I running into some conflicts when 2 or more users are running operations (when run an append query the second user will get the first info and the first user won't get anything) and I'm thinking it is time to make the database clientserver. Is there anything I should look out for when making this change or is it time to rewrite the whole program in VB? What is the easiest way to do this? I have about 10 users and the database is about 25mb.
I need to eventually create a YTD report with the following information, sorted by customer number and invoice number.
Here's my situation: If a customer is a current customer, their income amounts show up in the current customer column, and the commission on that amount is calculated based on all current customer income amounts added together, and then compared to a sliding scale. I have this working. If a new customer's income (for all their combined invoices) is <=200,000, I need to list the income for each of those invoices in a separate column for new customers. Once that customer's total income reaches 200,000, I need to display the income in new customer column up to that point, then any income over 200000 needs to be displayed in the current customer column. So if the amount 200,000 is reached in the middle of an invoice, part of it needs to be displayed in the new column, and part in the current. Also, the amount over 200,000 now needs to be added to the grand sum of all current customer income. I show a sample table below - any help would be greatly appreciated.
Every week by e-mail from a supplier one I get an excel file with 7000 or so data entries (special offers).
I import this into Access and then merge it with Word to produce a printed catalogue. A lot of this data duplicates week from week and the manual job of sorting through and deleting out previous entries all 7000 entries is getting too much (and is not reliable - I'm only human after all)!!
Is there a way (probably with a query) that I could automatically compare the latest table with the previous week' s table and produce a new table that only contains the changes (i.e. only the new data that has been added since the previous week).
This would make my job so much easier!!!
A primary key (?) could be the bar code or unique product reference.
I have a quick question. I have a field that has information that looks like the below. I need it to be in 2 separate columns, but there is no space between the numbers and text otherwise I would know how to do this in excel. Anyone know how to do this is excel or access?
I have a field on my report that contains positive and negative numbers. I'm trying to find a way to Sum the column twice for two different fields (one for positive sum and the other negative) To make this easy let me give a small example with explanation of the issue I am having. 14 rows of data containing positive and negative numbers in a field called [Rooms]:
The positive numbers represent confirmations, the negative numbers represent cancellations. I need 2 fields to separate these. The first field's control source is Sum([Rooms]). No problem there. Access does the math and comes up with "8" confirmations which is correct. Then in the second (Cancellation field) I try something like Sum([Rooms]<0) to get the negative numbers which should add up to -7 (-1+-1+-5), but instead I get -3 because it's not summing the values, it's really returning the number of rows that has a negative number. Is there any way this can be done at the ControlSource Property of a report field? Or if I could do a "Where" clause in a ControlSource I could acheive it another way, but I don't that is possible. Is this just something I'm missing with the way I'm stucturing the ControlSource or is it not possible. Or alternatively, is there a way to separate it in the query and have a separate query field for positive and negative going into the report. I can do one or the other in the query but I can't seem to do both without showing values of each type in both query fields.
I have a textbox on a form that i am pulling off of a table. the table stores the infor as listed below
customer ordernumber order description
Jeff 123 -abc -def -ghi Bill 456 -ghy -hig
but when i bring the field into a form it lists it out the field of order description as below in a textbox, i used textbox b/c the the text can be pretty long
-abc-def-ghi
how can i make it so that it separates out the listing as i have it on the table?
