My work has banned MS Access databases. I am finding it hard to manipulate data as required in MS Excel.
Attached is an excel workbook with 5 worksheets. The worksheet names are “PreviousFN”, “CurrentFN”,”Om issions”,” Additions” and “ChangedDetails ”
I receive a report every fortnight with 5,000+ records. The first thing I do is I delete all the data from ”PreviousFN”, copy the “CurrentFN”...
hrprabhu
Last Activity: Mar 13 '17, 12:28 AM
Joined: May 1 '10
Location:
-
What is equivalent to DLookup.... in Access?
I have attached two spreadsheets and a database.
E29 and E31 are dependent on D6 and E27 and I have to update the table (M1:Q60) every year from the tax office. This is for financial 2016.
I want to replicate this in the database.
How do I lookup do it?
Thanks a lot in advance....
E29 has this code....(in the database taxCoefP)
=IF($D$6="NO... -
Got it....
an unintentional space was the problem
" .pdf" instead of ".pdf" caused the problem. -
Trying to combine two pdf using access vba...
the bottom code is working.... Hard coded the document names
...Code:Sub CombinePDFs() Dim AcroApp As Acrobat.CAcroApp Dim Part1Document As Acrobat.CAcroPDDoc Dim Part2Document As Acrobat.CAcroPDDoc Dim numPages As Integer Set AcroApp = CreateObject("AcroExch.App") Set Part1Document = CreateObject("AcroExch.PDDoc") -
Apologies for the delay. When the big “C” strikes everything becomes out of focus.Leave a comment:
-
Split record....
I have to calculate something and the rate changes two or three times year. This year the dates of rate change are 12MAR15 AND 04NOV15.
when I input the "ToDate", I want to split the record
For example I want to calculate for the period 01FEB15 to 31DEC15,
when I input 31DEC15, I want the record to split up as follows creating two new records.
...Code:01FEB15 to 11MAR15 12MAR15 to 04NOV15
Last edited by zmbd; Oct 24 '15, 07:59 AM. Reason: [z{merged the related posts}{placed code format on tabular data] -
Thanks NeoPa for all your encouragement.. ..Leave a comment:
-
How do I requery the listbox after the update of the combox....
Hi All,
How do I requery the list box in the form after the combobox is updated? the name of the combo box is txtFilterClassN ame.
Regards
Raghu...Last edited by zmbd; Jul 12 '15, 02:51 PM. Reason: [Z{Unrequested attachments are discouraged for various reasons - Please clearly explain the issue instead - See you r Bytes.com Inbox for details}] -
Hi NeoPa,
I have been able to figure out and able to get my database to do what I want it to. It is infact doing better than I hoped for.
Thanks for all your encouragement.Leave a comment:
-
Hi NeoPa,
Finally I have been able to tweek it and the following is the part of the code.
...Code:Do Until .EOF If iPID <> .Fields("Pid") Then iCounter = 1 If Nz(.Fields("String2"), "") = "" Then .Edit .Fields("Sequence1") = 1 .Update EndLeave a comment:
-
Logic ....Code:Aut1 Seq1 Aut2 Seq2 Record 1 Aut11 Seq11 Aut12 Seq12 Record 2 Aut21 Seq21 Aut22 Seq22
...Code:for each Pid for Record 1 iCounter = 0 If Aut2 = Is Null then Seq12 = 0 iCounter = iCounter + Seq12 Seq11 = 1 iCounter = iCounter + Seq11 Else Seq12 = 1 If Aut12 = Aut11 Seq11 = 0 Else Seq11 = 2 End If End If If Aut12 = Is Not Null
Last edited by Rabbit; Jun 17 '15, 04:03 PM. Reason: Please use [code] and [/code] tags when posting code or formatted data.Leave a comment:
-
Thanks NeoPa, it is almost working except line 41 to 48 in the code. If string1 = string2 in the record, I don't want to update the number because it is close ended and can be deleted in one go.
Our pay system is antiquated and is nearly 30 years old and we are still stuck with it. I work for the federal government and things are not going to change for a long time into future.
So at your convenience please look at the...Leave a comment:
-
Basically there are 2 types of transactions
1. Open ended which means Date1and String1 is not blank but Date2 and String2 are.
2. Closed ended which means Date1, String1, Date2 and String2 are not blank. Date1 and Date2 could be same or different, but is String1 = String2
If the transactions are not put at the same time then Date1 and Date2 could be the same but String1 and String2 are not the same.
They...Leave a comment:
-
Thanks NeoPa,
Will post a sample database.
You are right. I put a conditional format to show if String2 = string1 then string1 is red in colour. I don't want it to be numbered.
other wise sub setLineNums is working fine....
Thanks.....Leave a comment:
-
Thanks a lot to everyone who has commented and answered. Basically these are certain transactions.
Where sequence2 and date2 are blank indicates the transaction is open ended.
Where sequence1 and sequence2 same the transaction was close ended and the input after the event.
Where the sequence1 and sequence2 are not the same, the closing transaction was input sometime after the opening transaction was input....Leave a comment:
-
How to number in sequence...
Hi All
I have a table in a database with 7 columns. The data is sorted by Date1 in descending order.
For each pid I want to put the sequence numbers
First record has two conditions
If string 2 is null then start numbering from sequence1
If string 2 is not null then start numbering from sequence2
If string 2 = string1 then
Sequence1 = 0
Second record has two conditions
Number... -
Thanks NeoPa...Access is like an ocean. I thought I knew a lot, now I feel I have just dipped my toes.
Thanks for your help...Leave a comment:
-
Hi Neopa
Got it working to what I want it to do.
It is toggling the values from positive to negative and vice versa.Code:Private Sub chkAddToTaxZone_AfterUpdate() Me.NoOfDays = (-1) * NoOfDays Me.FNTotal = (-1) * FNTotal End SubLeave a comment:
-
Thanks...Neopa Will tryLeave a comment:
-
Hi NeoPa
Basically toogle the values. If the check box is ticked, then the value should be positive if not negative.
Thanks for your time. Though I have been using Access since 1993, I did not start VBA till recently. Hence I am paying the price.Leave a comment:
No activity results to display
Leave a comment: