here's the dealio... i need perform an aggregate funtion off of a certain field, but it contains null values. i tried using Nz and an Iif IS NULL statement to get rid of the nulls. but both methods return the values as text. obviously, i can't get an Avg of these text values.
here's the two methods i tried:
Format(IIf([AvailTab].[Per00] Is Null,"1",[AvailTab].[Per00]),"Percent") AS Per01Test
Format(Nz([AvailTab].[Per00],"1"),"Percent") AS Per00
I have a form on which I need to print a sequential number - increasing by 1 each time the form is printed. I cannot think of a way to do that - perhaps something returned from a query? Any suggestions?
I have a query which runs fine, however one of the outputs is a calculated field and i use the Format(someValue,"Fixed") method of specifying the format for the % Change .Here is the query
The problem is that the % Change calculated field for some reason comes out as a String. i know this because when I try and sort on that column, it sorts it as if it is text and not a number value i.e. it doesn't put it in Ascending order.
I am having trouble with the below returning the correct number of records, and can't see why.
I have one table, tblDevice, which has 4 columns, ID | DeviceRecNo | ExcludeFromCheck | StockLocationID
ID - Autonumber DeviceRecNo - Number ExcludeFromCheck - Number (1 = yes, 0 - No) StockLocationID - Number
I have the following running as part of some code, but it is not returning the correct number of records, and I cant see why not. I have tried creating this in a query in Access itself, and copy the SQL into VBA and it still doesn't return the correct number records.
I have 4 records in there for testing, all with StockLocationID = 3, all with different DeviceRecNo, and two each of ExcludeFromCheck, 2x0 and 2x1
Code: Public Function test() Dim db As dao.Database Dim rs1 As dao.Recordset Set db = CurrentDb Set rs1 = db.OpenRecordset("SELECT DeviceRecNo FROM tblDevice WHERE StockLocationID = 3 AND ExcludeFromCheck = 'No'") Debug.Print rs1.RecordCount End Function
I have a Dlookup which returns the correct value but returns the text of the instead of a numeric value. I have triple checked that the fields are all numeric. I suspect there is something wrong with my Dlookup syntax as it is my biggest weakness. In the following syntax what could be wrong?
DLookUp("SSER","TABSSS",[grosspay] & " Between [TABSSS].[Minrange] And [TABSSS].[Maxrange]")
I have a form used for taking inventory. One table to lookup items, one for writing values. Tables linked by an ID #. I created a text box on my form to lookup a value called PARQTY in the lookup table. I need to be able to subtract that value from the QTY that is entered into the Qty_Counted box on teh same form when they take inventory.
I created a new Text box and in the control source for it, I entered an expresion that basically says subtract The PARQTY from the QTY_COUNTED it reads = [QTY_COUNTED] - [PARQTY]
The box is subtracting the QTY_COUNTED from the ID # that is linking my two tables and displaying it. Its not supposed to be subtracting the QTY_COUNTED at all. It should be subtracting the PARQTY value that is displayed from the QTY_COUNTED. The PARQTY box does display the right value from the lookup table so I can't figure out why it's pulling that ID number or where from. The rest of the form / program works and we've been using it for a few years. I also created that, but like now, I had to muddle through each and every little thing I did.
I am using Access 2007. Second, I am using two tables, Inventory and Service Request. Inventory is a list all the Inventory, with has things like location (building & room number), type (desktop, laptop, etc.) and access tag number. Service Request also has Access Tag Number (should link back to Inventory) & date of reported/resolved problem and description of problem/solution.
On my form for Service Request, I have the access tag number as a fill text box, when you double click on that text box, it runs a Query that asks for the 1) Building, 2) Room Number & 3) Type . . . Query currently opens in a the spread sheet view that shows those three things but also the Access Tag Number associated to them.
I would like that Access Tag Number to just return into the field that was double click to start the query.
Date of Birth (DOB) field etc. in one program are text - how do I make another file with the same data into number fields for Date of Birth field etc? When I copy data to file that has number fields the 09252004 is changed to 9252004. Can I get reports with the correct Date of Birth in them by moving data from text file to number file?
There is data entered monthly in file and formula has been set up for January, February etc as ---quarter: Int(([month]-1)/3)+1. I would like formula for the fiscal year for April to be counted as month 1, May - month 2, June as month 3, July as month 4, August as month 5, Sept as month 6, October as month 7, Nov as month 8, Dec as month 9, Jan as month 10, Feb as month 11 and March as month 12.
I am trying to format an excel spreadsheet through access, specifically trying to convert a column from text to dates (I receive the data in text format and need to translate it to dates).
Code: Dim excelApp As Object Set excelApp = CreateObject("Excel.Application") excelApp.screenupdating = False excelApp.Visible = False Set excelWB = excelApp.workbooks.Open("Z:DataBasicSMData.xlsx")
[Code] ....
I'm no longer getting an error, but it isn't actually modifying the spreadsheet...
XXXXX-X (x are digits), for example - 4 01-1, 4 01-2...
I need find field with text beginning 4 01 and max of last symbol. Last symbol +1, then again convert to text field. For example: for example 4 01-1, 4 01-2,4 01-3 - must find 4 01-3 , then 3+1=4 and 4 01-4 format again to text.
Please, help me with formatting. Thank You in advance.
I have a small question. There is a table that I am trying to use that houses product numbers, the only problem is that those product numbers start with a '0' but the '0' got deleted because the numbers are housed in a number defined field. If I change the table properties to text, is there a way I can add a leading '0' infront of every existing product number?
I'd like to take a branch number field, let's assume it is "223" and change it to "223 No Com". Is there a way to add the "No Com" on the end of it like that?
I have a table with the following fields: Tool Text Tool_ID Text Description Text
The Tool_ID field holds a number if the tool provides a tool id. However, if the tool doesn't provide a tool id, the text "No tool ID given" is automatically placed into the field. Therefore, it needs to remain a text field for this reason and others that are too long to go into. Anyway, I created a query that pulls only one specific tool which always provides a tool ID. Therefore, the results of my query will always have a number in the text field "Tool_ID".
Question: Is there a way to convert the text field to a number field within the query?
I have a form with four combo box that will search for a record, as follow:
Field Name "Printer" , DataType "Text" Field Name Month" , DataType "Text" Field Name "Year" , DataType "Number" Field Name "Day" , DataType "Number"
code for Find Record Button:
If Not IsNull(ID) And Trim(ID) <> "" Then StrSQL = StrSQL & " And [Id] = " & [ID] End If
If Not IsNull([txtname]) And Trim([txtname]) <> "" Then If InStr([txtname], "*") = 0 Then
StrSQL = StrSQL & " And [Name] = '" & [txtname] & "'" Else StrSQL = StrSQL & " And [Name] like '" & [txtname] & "'" End If End If
'================================'
If Not IsNull([txtyear]) And Trim([txtyear]) <> "" Then
If InStr([txtyear], "*") = 0 Then
StrSQL = StrSQL & " And [Year] = '" & [txtyear] & "'" Else StrSQL = StrSQL & " And [Year] like '" & [txtyear] & "'" End If
End If
'================================'
If Not IsNull([txtmonth]) And Trim([txtmonth]) <> "" Then
If InStr([txtmonth], "*") = 0 Then
StrSQL = StrSQL & " And [Month] = '" & [txtmonth] & "'" Else StrSQL = StrSQL & " And [Month] like '" & [txtmonth] & "'" End If
End If
'================================'
If Not IsNull([txtday]) And Trim([txtday]) <> "" Then
If InStr([txtday], "*") = 0 Then StrSQL = StrSQL & " And [Day] = '" & [txtday] & "'" Else StrSQL = StrSQL & " And [Day] like '" & [txtday] & "'" End If
End If
'================================'
This is only a part of the code, I have a problem with year and day since they are numbers not text I was told that i cant use this line:
StrSQL = StrSQL & " And [Month] like '" & [txtmonth] & "'"
I was told that there is no need to use "like", am not really sure how to do it, any help will be appreciated.
Is it possible to actually change a number to text? I cant seem to figure out how. I have 2 proprietary systems one uses a number and one uses text for the same item. I know how to change the text to a number however the more efficient way for me to do this is the other way around. Its the differnce from 300 to 400 record change vs 4M. Any help would be great thx.
Hi guys and gals, I have compiled a very simple database to track staff and training audit requirements for my company. I am 57 thick as 2 short planks and need some help please. I wish to ask a text box called "Score" to return a number 2 if a yes/No box is "NO" and a number "1" if the Yes/No box is "YES". I have looked at the help sections of Access and thought maybe an "IIf" statment in the Validation Rule section of the "Score" box might do the trick. However, i haven't got a clue as to what the coding should be written like. Can anybody help me please.
Basically i want to say "If YES/NO = NO then SCORE = 2 but If YES/NO = YES then SCORE = 1" Told you i was thick
I have a peculiar problem: I have a form for filling in details of drawings received. one of the controls is 'Drawing Number'. The drawing number is usually an alpha-numberic 'number' like AC234 or MC2 or TPD-00113, with no generalizable format.
once the number is put in and saved, I would like VB to automatically offer to save the next number, for example, AC235 or MC3 or TPD-00114. I want to do this because usually there are hundreds of sequential drawings and I want to reduce the risk of typos as well as make the input easy.
I want VB to be able to take the number, split it into alphabets and numbers, add 1 to the number, put it back with the same alphabet and re-populate the text box.
I tried doing that using last two digits (right(me.txtbx, 2)) but that method is full of problems. For example, it goes from MC009 to MC0010 instead of MC010 and so on.
I'm having trouble swicthing the data type of my numbers.When I am importing the table from another database it enters as a text.When I then go to table design and try to switch it to number, it removes the decimals and switches the value to something ALOT higher.how to fix this so that it just enters as a number with the same value?
I Have Copied The Text Boxes So The Information Is Automatically Displayed To Create The Client Reference, But Rather Than Shortening The Copied Text Boxes To Show Only The First Few Characters Is There A Way Of Limiting The Number Of Characters Shown In The Copied Text Boxes??
Hello, I have a text file I'm querying that stores a field as a text. ComponentID:0000000242
I'm inserting this data into a table that stores this value as a number. Thus truncating the leading zeros. ComponentID:242
I would like to create a query on the table using an InnerJoin on the text file column:ComponentID:0000000242 and the table column:ComponentID:242 as the unique identifier. Doing this join gives a case error because of the different data types.
Can format ComponentID using a query so it reads from my table as 0000000242 and do an InnerJoin to the text file. In vb String= Format(242, "0000000000") But I'm finding it difficult to get the query to format this correctly.
Unfortunately, i'm unable to change the column data type in the table structure and am looking for a work around. Is this even possible? Any ideas?
I want to have a question in my query that does not get answered by date but by a concatenated field named "MonthYearPeriod".
We always forget how many days in a month. But we remember the month and year. So for say a Sales Range of Nov 2006 to Oct 2007, we prefer to respond to the criteria question as: 11-2006 and then 10-2007.
FiscalMonth and FiscalYear are both Long Integer number fields in the table.
Query field concatenated: MonthYearPeriod: [FiscalMonth] & "-" & [FiscalYear]
criteria line: Between [Month-Year START DATE] And [Month-Year END DATE]
However, i get other crazy time periods. So this is not working. Maybe on the Query field box, I need extra coding to convert the number to text?
I assume that a Dash sign between the month and year are fine. Thanks
looked around but could not find the answer. i have a text box that might or might not contain text and number data. an example of the data in the text box might be;
bob roberts, 17/03/2006, aged 23
what i would like is some VB code that would:
check anywhere in the text box for number characters that are 8 characters in length with a comma at the end [12345678,] as in the first example. and change this to 12/34/5678,
bob roberts, 17032006, aged 23 would become 'on lost focus' bob roberts, 17/03/2006, aged 23
