All,
I have a Postcode field in my table and I want to be able to check the data to make sure that it is a correct UK postcode.
Is there away that I can find out the format of the data, to be able to run a query against it, or is there a better way of doing it?
I need to account for all types of UK postcodes (A1 2BC, A12 3BC, AB12 3CD, WC2A 3BC). There are also foreign postcodes in this field.
Help appreciated!
I wanted to assign the field "Number of magazine" with special format based on date/time format but showing only year and month in the format: "yyyy-mm".
So in property of this field in format I put yyyy-mm and in input mask I type 0000-00;;-
I also created the form based on the table containing above field and I defined format and input mask for corresponding formant in the same way like at the table.
But if I try to type date for example 2014-01 in text box of the form it comes up with the full date 2014-01-01. Why does it do like this? What do I do incorrectly?
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 calculated field in my query that takes the difference between to incomes. I want to format this field to currency. How can this be done?
View 1 Replies View RelatedI have a query that calculates a sum like this:
Sum: [table1]![field1]*[table1]![field2]
I would like result to have 2 decimal places.
I tried to use formatnumber like this:
Formatnumber(Sum: [table1]![field1]*[table1]![field2];2)
But I get syntax error. please help with the correct formula.
I have a field in a query that contains numbers and text (text field). The numbers displayed come from a percent calculation and display with many decimals ie, .99898745987245. Is there a way to eliminate the decimals with code in the query field? For example .99898745987245 to equal 99%? I can’t format the field as a number or percent because it has both text and numbers. HELP!!
Thanks
DB Setup:
Table1: I have a table (Vendor) that has 2 fields (# & Name) with # being an AutoNumber. So only Name is being input via a form. I have formatted the autonumber field as 000;(000).
Table2: A table that is populated via form with invoice info etc and vendor number is added through a drop down combo box (which also has the above format on it)
Table3: Similar to table2, with slightly diff info but still vendor #
Query1: Is a make table that consolidates table 1 & 2 via union on like fields (ie vendor #) This make table also has the format from above in its properties field, although when i open the table it makes (Table4) the vendor field is not formatted as i need it. So 3 appears as 3 not 003.
Query2: takes table4 adds some extra info and exports file (as txt or xls)using outputTo & TransferText macro so that it can be loaded into a Hyperion Essbase system
My problem is that although the field value is formatted as 003 in appearance, when i take it to excel it changes back to 3 when i need it to stay as 003. I would like the make table query to also format the tables field as 000. Is the problem with the autonumber in the orig table or is it simply excel being stubborn when i take it there. If i changed the vendor field to text string in the make table would i still be able to link it back to the orig vendor table to get the names etc (ie number field linked to text field??)
Thanks in advance
Hi everyone,
Please would someone be able to help me?
I have created a union query however, one of the columns, has not picked up the same format as it has in the tables. As in the tables it has this format
'00000'.
Please woud you be able to advise me how I can change the format on one of the 'columns' in my union query. As one column is 'numbers' and the other is 'text'. I need to change the number column so the format is '00000'.
Thank you in advance for your help.
Nats
I'm having a format problem. I have a query (Q1) that, among other things format a date field as YYYYMM (Field1).
I have a second query (Q2) whose data source is Q1.
In Q2 I need to link Q1 to a table but Field1 is being reformatted as text (confirmed by running a make table query).
I want Field1 to be Number format to eliminate a mismatch error.
I know how to change the format of a field in a table using VBA but I cant seem to find a way of changing the format within a query.
I have numerous databases that I use with make tables in there, these will often contain Currency values that we need to be set as just General Numbers. We can get it to work in the Query but whenever we run the query, the table it makes always shows up as currency.
Is there a way so that the table created will automatically be just General numbers...
I have a query that add up the numeric values in a value list assinged in a combo box in response to each question, the row source for question 15 for exmaple is as follows:
ROW SOURCE: 0;"I have no idea";5;"I indicated that I wouldn’t have time today";0;"Was mentioned early on and then not offered again";5;"The salesperson said the vehicle wasn’t available"
I then run a query that adds together the responses to 3 questions, including question 15, the field in the query appearing as follows:
LS5: ([q14]+[q15]+[q16])
It was working fine but has stopped working, the fault lies with q15, if I take it out it works again. So I looked at the table as I am sure it must be the way it is set up, why it worked before I don't know and I attach a screen shot of how the field is set up in the table, which is no different to q14 and q16.
Anyone got any ideas?
One last thing is that it makes no difference if the fileds contain a number (including zero) or are blank
I have a simple query to calcualte a profit margin on daily sales lines and I use a quick and dirty expression to calculate the margin in the query so I never need to drill it down further than that level (I don't want to go as far as putting the output into a report as it is only for use when double checking lines for errors which get fixed there and then in the database).
So far so good, however the margin output is a bit awkward to read as I can't seem to format it as a simple percentage. The field properties page doesn't like doing anything with the expression and even typing in a format manually has no effect, so I end up with figures like
36.7768595041322
38.6666666666667
15.6448202959831
etc
the expression i use is:
Margin: IIf([dbo_tbl_sales_invoice_lines.price]=0,"",([dbo_tbl_sales_invoice_lines.price]-[net_cost])/[dbo_tbl_sales_invoice_lines.price]*100)
Is there any way to format this output to show only 1-2 decimal places and be in a proper number format so I can sort them in ascending order properly?
Im running a query and normally there is only a field-name in heading. I have
multiple tables with equal field names. Now I want to get table names in
heading too (Tablename.Fieldname) so I can make difference between fields on each tables when previewing query. Is this possible in access? I don't want to change all field-names manually, I know it's possible and done easily, but there are almost hundred fieldnames...
Im finding a solution. Help me please. Thanks :eek:
- Roger
--
Have a nice day.
I am using an append query to move data into another database. One of the fields being imported is a date field in text form (20141201). I need it appear in the final database in text form (01/12/14) I have tried using several date conversions and cant get this work. Ideally i need the final value as a text rather than date.
View 8 Replies View RelatedI have two tables that are joined - called A and B. A has two fields, "PLACE" and "RAND" and is joined to B via field "RAND". Other than "RAND", B has several fields named 01 TOTAL, 02 TOTAL, 03 TOTAL, etc...for about 60 fields.
The "PLACE" field in table A has data that is 01, 02, 03, ect.... How do I structure a query so that whatever "PLACE" is, I can match the XX TOTAL value? In other words, i need to have the query field header be somehow dependent on the value in "PLACE".
Is this even possible?
I'm trying to create a new field based on two existing fields as part of a select query. Two tables in the query each have a "HOSPITAL" field which is an indicator for "Y" or "N" for each department (in the DEPTLOOKUP table) and for each facility (in the Facility Lookup table). I want to create a field whereby a new indicator is created so that a Y is given for each record only where the facility and department HOSPITAL indicator field is both "Y". I have attached a word document that has a screenshot of the query in design view as well as the access sql code.
View 3 Replies View RelatedHello All,
I have searched the forum but I don't quite get what I am looking for.
I have a subform that has these 2 fields:
- Status
- LastFollowup
What I want is if the Status shows "Pending" and the date entered on the LastFollowup is more than 7 days. Then it should turn Yellow.
I tried this code in the expression box but it doesn't seem to work
[Status]="Pending" and [LastFollowup]>7
Could anyone please assist? I know this is simple but it's not working for me.
Thank you
Hi,
I am trying to write a query that looks at a set of parts that are assigned to the same "Asset" number, and takes the sum of all prices for that collection of parts.
I have the query set up to do this without any problem. Where I am stuck is that in the table containing the details of parts, there are three possible prices. What I need it to do is take price c if there is no value in prices b or a, price b if there is no price in price a (otherwise take price a).
I have tried this using the criteria ="price a" = 0 AND "price b" = 0 (for price c)
and ="price a" = 0 (for price b criteria). When I run the query I get a "Data type mismatch in query expression" error.
Can anyone advise?
Thanks,
Ryan
Hi,
I have a query which returns 1 value (The description associated with the maximum ID in the table).
I have a Text box on a form and I would like the default value of the text box to be the the value from the above query.
I have tried to build an expression using the expression builder by selecting the query and the field that I am interested in, in that query but I can't seem to get it working.
Could anyone please advise how I can assign a result from a query to the default value of a field on a form.
Many Thanks
I would like to know what criteria is used to return records based on a field with data type attachment.
I am using "IsNull" and "IsEmpty" but neither of these work.
Maybe it's the day's 'brain drain', but I need to set a criteria in a query whereby it will select answers in a field that are a specific number of characters in length.
i.e., answers that are 5 digits long (without knowing any of the digits)
Russ
I want to create an query that will update a field value for records where the length of the field value is less than a given number (if LEN of UniqueID is less than 6 for example), but I can't figure out how to write a SELECT query, much less an UPDATE query for this condition....can someone help me, please?
View 1 Replies View RelatedHi Guys, I'm new here and fairly new to anything indepth with Access.
I'm using Access 2003 and have a table which has a number of customer records. The two relevant fields are the CustomerID which is a simple integer and OrderDate obviously this is a date field!
What I need (to link in with the other forms) is a query that will return one record for each CustomerID it should be the most recent OrderDate.
I've had a few attempts and done a couple of searches but can't seem to find exactly what I'm looking for. If someone could point me in the right direction I'm happy to play with sample code and read up on bits other people have done (I tend to learn better that way) rather than just being given some code!
Thanks for any help you could can provide.
the relevant fields look something like this:
TellerNum - Date - Field1 - Field2 - Field2
I need a way for my query to take all rows with the same TellerNum and add Field1, Field2, and Field3 together for those records. So, there may be 20 records with the same TellerNum, and I need Field 1, 2, and 3 to be added together and then totaled for all 20 records to give me 1 grand total. I need this to be done for each TellerNum, so that the results will be a grand total for each TellerNum. So, the results may look something like this:
TellerNum 1: 486
TellerNum 2: 300
TellerNum 3: 240
etc..
I would like to know what criteria is used to return records based on a field with data type attachment.
I am using "IsNull" and "IsEmpty" but neither of these work.
I am importing data to a program that requires the life of an item. This is in months such as 241 months. The valid format for this program is YYMM. 241 = 2001 or 20 years 01 month.
Another example: 16 months should read 0104 or one year 4 months.
Any idea how to convert this to yymm format based on the number of months?
thanks in advance.