I'm trying to compare a Currency formatted field to a Number formatted one and getting wrong results.other then formatting the Number as Currency too ? Tried to use Val() but the Currency returned 0
View Repliesdear frnd,
i have a access program for printing cheque. i want to use a text box which automaticaly convert number ito digits from user input text box.
Is it possible to convert a number (whether in the "Standard" or "Currency" format) into words ?
View 5 Replies View RelatedI have a set of numbers, say (these could also be values of a column in a table)
FieldA = { 11, 16, 20, 23, 30, 37, 40, 50 }
I have a number, say 196.
I want {16,23,30,37,40,50} to be returned as these numbers add up to 196.
Note :
1) There is no possibility of two solutions with the kind of numbers that I may be using.
2) A solution using excel is also OK, though, personally I would prefer access.
Edit :
3) {16,23,30,37,40,50} - Each value is a separate record i.e. they are not in a single field, rather :
16
23
30
37
40
50
I was wondering can MS Access do a range compair look up. I have 2 tables. One is the Info table and the other is Rate table. The Info table has 3 columns like this:
[code]...
I want access to read the Info table and pick up the values in the FICO, LTV and Type and compair it. Like with the above example. In the Info table FICO = 622. So then I want it to carry the number 622 down into the Rate table and compare that the number 622 is in FromFICO and ToFICO column to find where does 622 falls in between. In this case there are nine rows in the Rate table that 622 is between 620 and 629. The next criteria is the LTV number in the Info table. It's 76. So back into the Rate table looking for the number 76. And 76 is happen to fall between 75.01 and 80.00 in the FromLTV column and ToLTV column. Next back to the Info table is the Type column which is 2. Now back to the Rate table to look up the Type column for Type = 2. So from FICO = 622 , LTV = 76 , Type = 2...So the rate I want to populate is 0.25...So is this possible to do in Access?
how to change default currency in Access 2013 to a "foreign currency" (i.e. to Indonesia Rupiahs) without changing control panel (Region/Language>currency) - now setup as USA ($US)?
View 3 Replies View Relatedadd a format to the Sql select for currency.This is just a section of the full code before
Code:
Function RunLWSCA()
Dim cnn As ADODB.Connection
Dim rstUplo As New ADODB.Recordset
Dim rstDiag As New ADODB.Recordset
Dim rstTemp As New ADODB.Recordset
Dim SQL As String
Dim strDiag
[code]...
I have a Concatenate string of text and currency. When joined together, the currency format disappears. I need to reformat so the string stays together with new format. Below is what I have:
Public Function ConcatAgreementFundsCommitted(ID As String) As String
'/ Purpose: Generate a concatenated string of Tracks for selected line.
On Error GoTo Err_Handler
Dim lngLen As Long 'Length of string.
Dim strOut As String 'Output string to concatenate to.
[code]....
I need 'Funds_Committed' to display as currency.
The following code gives me error message. There is problem in Format function. How can I write using VBA that "Scantime is less than 08:00"
Code:
strsql = "SELECT Count(BatchNo) AS CountOfBatchNo " _
& "FROM Table1 " _
& "GROUP BY ScanDate " _
& "HAVING ScanDate=" & J & " and Scantime< format("08:00:00","hh:mm:ss")
one more thread of mine today
I have this table:
-> tblTest
Code:
test_id | test_Name | test_Date | test_value
------------------------------------
1 | Company A | 01.01.2010 | 0,90
2 | Company A | 02.01.2010 | 0,95
3 | Company A | 03.01.2010 | 1,10
4 | Company A | 05.01.2010 | 1,05
5 | Company B | 01.01.2010 | 0,9
6 | Company B | 02.01.2010 | 1,20
7 | Company B | 03.01.2010 | 1,15
8 | Company B | 05.01.2010 | 0,95
9 | Company B | 07.01.2010 | 0,80
The important level is 1,00
Now I will, for each company, check:
- Is the value of ID-1 >1 AND the value of the ID-2 <1, THAN give me a 1
- Is the value of ID-1 <1 AND the value of the ID-2 >1, THAN give me a -1
In this example the result should look like this:
Code:
test_id | test_Name | test_Date | test_value | test_Code_result
-------------------------------------------------------------
1 | Company A | 01.01.2010 | 0,90 |
2 | Company A | 02.01.2010 | 0,95 |
3 | Company A | 03.01.2010 | 1,10 |
4 | Company A | 05.01.2010 | 1,05 | 1
5 | Company B | 01.01.2010 | 0,90 |
6 | Company B | 02.01.2010 | 1,20 |
7 | Company B | 03.01.2010 | 1,15 | 1
8 | Company B | 05.01.2010 | 0,95 |
9 | Company B | 07.01.2010 | 0,80 | -1
Probably I have to carry out this in vba.but i dont know how I can calculate across the datapoints (rows).
So I have a table:
table1: company_name, year_month, company_rating, rating_change
For each company, I would like to compare the rating from 201201 and 201202 (and keep going for each month) and if there's a change in the rating, update rating_change to the difference in rating values.
The company_rating is a value from 1 to 20.
How should I go about doing that?
I want to be able to compare two tables to see whether their Column Names & sequence are the same.
View 1 Replies View RelatedI would like to compare the values of several columns in the same record and remove any duplicates. So...
ID | Col1 | Col2 | Col3 | Col4 | Col5
-------------------------------------------
1 | A | B | C | D | C
2 | C | C | C | D | D
3 | A | A | B | A | D
In the above example I would remove one of the Cs in row 1.
Two Cs and one D in row 2 and two As in row 3.
At the moment I have the value of each column stored in a variable and each one is compared against each other using LOTS of If statements.
I am making a dp for end of day sales, and i want to compare data in a table to the numbers inputted in a form and see whether this value is higher or lower. the table displays the target sales figures for each day, and the form allows the area mangers to enter in the current day figures.
View 3 Replies View RelatedI'm working on creating an events log. In my table I've got a field that pulls the individual's username (that they use to log into the computer) and logs it against their log entry.
As part of my query (for my daily events log report) I'm pulling up said username.
I'd like to give access to the specific user to edit his (and only his) entries from the report, but I'm getting a type mismatch with the below code:
Code:
If me.UsernameID = "KraussLJ" Or Environ$("Username") Then
DoCmd.OpenForm "frmShiftLog"
DoCmd.FindRecord Me.ID, acStart, , acSearchAll, , acAll
Else
MsgBox ("You are not authorized to edit this entry")
End If
(before you ask, "KraussLJ" is our ops manager who insists on having final editorial control over the shift log before sending it higher up the chain)
I can get the msgbox to run if I block out the
Code : Environ$("Username")
, but beyond that I'm a little stuck. Should this be a dlookup function or am I overanalyzing this way too much?
Compile error: Method or data member not found
Code:
Option Compare Database
Option Explicit
Dim intItems As Integer
Dim strName() As String, datBirthDate() As Date, intAge() As Integer
Private Sub Form_Load()
intItems = Val(InputBox("How many names do you have?", "Name List"))
[Code] .....
I am trying to concatinate string in a loop to generate a dynamic SQL to compare 2 tables. But I am not getting the result I want
Code:
Set rs = CurrentDb.OpenRecordset("r1")
Set rs2 = CurrentDb.OpenRecordset("r1_old")
columnCount = CurrentDb.TableDefs("r1").Fields.count
strSQL = ""
For I = 0 To columnCount
[Code] .....
I am trying to achieve something that reads like the following
r1.Field(0) = r2.Field(0) AND
r1.Field(1) = r2.Field(1) AND
r1.Field(2) = r2.Field(2) AND
.....
I have a form where when the user clicks on the browse button then excel workbook filepath gets stored in the textbox as below:
Code:
Private Sub CommandButton1_Click()
ChooseFile
End Sub
Sub ChooseFile()
Dim fd As FileDialog
Set fd = Application.FileDialog(msoFileDialogFilePicker)
[Code] .....
Please see attached the excel workbook. Everytime the user will select Excel workbook using Browse button. Now in that file , the first sheetname will always be "Summary". I want to perform the following steps:
1. So now I want VBA code to copy the data from columns "Withdrawn","Obsolete","Updated","LitRef" from Summary sheet to the Access table named tblSummary.
2. When the data gets copied in Access table then write So VBA code that will check if the data in field LitRef in table "tblSummary" is present in field "Reference" of Access table "tblliterature" . if its present then check in the tblSummary , which corresponding fields out of "Withdrawn","Obsolete" and "Updated" stores "Y" .
3. If "Withdrawn" field value is "Y" then change the status of corresponding record of tblliteraure to "Withdrawn"
4. If "Obsolete" field value is "Y" then change the status of corresponding record of tblliteraure to "Obsolete"
5. If "Updated" field value is "Y" then change the status of corresponding record of tblliteraure to "Updated" .
I am building a tracking database where we would be able to track information which field are null data and report them to our Administration to fill the null data. For this I have created a form name "Search" and I have a combo box control on that form which is bound to "Table = employee" and its ROW SOURCE TYPE="Field List", I would like to able to query records where the selected value in this combo box is null through out the table.
For example if I select "Telephone" from this combo box dropdown, I would like the query to show all the records where the "Telephone" is null, how to set the criteria in query to take the combo box value as "Field Name" and then compare it with the Field/Column in the table and show the null values.
creating sequence number in a query using a starting number. For example i want to use the starting sequence number as 5
---SEQNUM---------COLOR-----------
-----5------------------blue--------------
-----6------------------red---------------
-----7------------------orange-----------
-----8------------------black-------------
I have to write a code for my database,i have folder with files "pending Review" and a table with column "tblExcelLocation". when i run my database all the files from pending review folder goes to "tblExcelLocation" on a click of button.But,if the files already exists it should not insert those files and insert the rest.For this i tried to write a code but i think i m unable to do that .
Code:
Loop through files in folder
folderspec = "O:QA FilesQC ReportingPending Review"
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder(folderspec)
Set fc = f.files
[code]...
I have attached a sample database. Basically I want to have some lines of code that generate the result table, which is tbl_readmit_result.
As you can see, the difference is the addition of a new column called re_admit_status.
Rule is:
Status = "y" when the admission date, compared to the previous discharge is less than 7 days, otherwise "n", for the same pt_id. You cannot compare the two dates on different pt_ids.
Is there a way that this can be done automatically without having to go through the record manually?
The following code copies data from Access table to Excelsheet1. But the thing is when it combines Type,Type1 and Type2 in the query then i want to put some condition as below:
if Type1 value is less than 10 then put 0 before Type value
if Type2 value is less than 10 then put 0 before Type1 value.
e.g Type=5, Type1=8, Type 2=19 then in the sheet1 it should show: 50819 but at the moment it shows 5819.
Code:
strsql = "Select CDate(Format(Scandate,""0000-00-00"")) As [Scan_Date],batchno,ScanBoxID,envelopes,cases,pages,Type & Type1 & Type2 from Table1 where (Type=2 or type=3) and scandate =" & J
rs.Open strsql, cn
Set ws = ThisWorkbook.Worksheets("Sheet1")
ws.Range("b8:I1000").Clear
ws.Cells(8, "B").CopyFromRecordset rs
ws.Cells(2, "G").Value = J
rs.Close
I have set up a number field to enter in the amount of money we have spent on a particular project, and when I enter, say £645.18, once I press TAB to move to the next field, it reverts to £645....
I have the decimal places thing set at 2, and the format on currency......
I'm trying to create some code that will create a new folder depending on the ID that is currently being added to the database.What happens at the moment is a new ID is generated which in turn is job reference.When this is added to the database a folder is created with a prefix of this ID number and a 20char test specified in a text field by the user.
What I am trying to achieve is this:If the ID = 57...Then a folder is create called 1 - 500 (and ignore if one has already been created, which it should have at ID number 1)...When the ID Number 501 comes along another new folder is create called 501 - 1000 etc etc.The idea is not to have 3000+ folders in just one folder making it look a bit messy and lengthy to look through.
I'm using VBA to insert the page number in a header. It's working, exept for a couple of things. I want it to insert the page number at the current position, and restart numbering at each section. I increment the section # with the variable x. Currently, it's inserting the page number at top-left of header. I want it to insert the page number after the word "Page" followed by 2 lines breaks.
Here's snippet of my code:
'Create Header
With ActiveDocument.Sections(x)
.Headers(wdHeaderFooterPrimary).LinkToPrevious = False
.PageSetup.DifferentFirstPageHeaderFooter = True
.Headers(wdHeaderFooterPrimary).Range.Font.Size = 9
.Headers(wdHeaderFooterPrimary).Range.Text = "Session: [" & rst!SessionNum & "] " & rst!SessionTitle & _
Chr(13) & "Presenter: " & rst!Full_Name & Chr(13) & "Page " & Chr(13) & Chr(13)
.Headers(wdHeaderFooterPrimary).PageNumbers.Add pagenumberalignment:=wdAlignPageNumberLeft, FirstPage:=True
End With