I found a way to do it without linking the spreadsheets though, I used record macro to input this a new column with this code
Code:
ActiveCell.FormulaR1C1 = _
"=IF(ISERROR(MATCH(C[-50],Sheet1!C[-50],0)),"""",""Duplicate"")"
shawnnnnnnn
Last Activity: Feb 6 '15, 03:20 AM
Joined: Nov 10 '14
Location:
-
Hi guys, thanks for all your input! Really appreciate it :)
I found a way to do it without linking the spreadsheets though, I used record macro to input this a new column with this code
With that code, I recorded another macro that will filter out all the rows... -
I don't think the spreadsheets are linked to any database. Is there any other way I can go about doing this without linking the spreadsheets to an sql server?Leave a comment:
-
Advice on how to start on my code
Hi all,
I've been tasked to write a macro that will compare 2 different worksheet. The first worksheet contains the past employee list, whereas the second worksheet contains the current employee list as of today.
The main idea for the macro is that it will look through both lists and identify the new and current employees. The current employees will be inserted into a new worksheet and the new employees will be inserted... -
Well, I might have found a solution, but it'll mean more work for the user. I have to open the text log file in notepad++, copy the entire thing and paste it in a new file in notepad++ and import that file instead.Leave a comment:
-
Hi twinny,
I've already tried those methods and it still can't work. I know this is highly unlikely, but could it be a hardware/software issue?Leave a comment:
-
It's actually a unicode file, however, I've already checked through and I don't think there are any special characters in it.Leave a comment:
-
Well, I managed to find some lines that were not inputted into the the table.
The lines are:
" :61:1410311031D 22,50NSC-SW000000//SC-SW"
" :86:M04"
" 000000"
" :62F:C141031USD 746743,53"
" :64:C141031USD7 46743,53 )]~"
Is there a possibility that the code is unable to read lines that start with " :"?Leave a comment:
-
Is there an error with my FileSystemObject codes?
Hi all,
I'm currently facing an issue with my codes. I am required to import a text file line by line into access, however, when I check the number of records in Access, it does not match the number of lines in the text file.
E.g. My text file may have 519420 lines of text, but my records only show 518785.
I need my entire text file to be imported line by line in the exact order.
This is... -
I managed to solve it already. I used the debug print to check my Task and it turns out that it was executing the first if statement as the date range was blank.Leave a comment:
-
How do I code the multiple form's function to suit these needs?
Hi all
I have a table containing [Timestamp], [AgentName], [SNOCAcknowledge d], [Details], [EventType].
I need to create a multiple item form that counts the records based on event type and displays it like in the image.
The form will be able to even count the number of event types by a date range and/or if it was acknowledged or not.
As you can see in the image, I have successfully... -
How to search by date range in Access?
Hi all,
I am trying to create a form that will select records by date range. The form contains 2 textboxes, with pop-up calendars to select dates. I managed to come up with this code, however, it's not working. Whenever I run the code, my database just goes blank, as though it's selecting something that is not in the records.
...Code:Private Sub Command16_Click() Dim Task As String Dim startDate As Date -
UPDATE:
I think the problem lies with the code that checks the agent name. When I removed it, it managed to populate the EventType column based on the details. But I still need to find out how to check the agent name too.Leave a comment:
-
shawnnnnnnn started a topic How to input a string in another column based on the data in another column?in AccessHow to input a string in another column based on the data in another column?
Hi all,
I need to input a string into a column named "EventType" . The code should first check if the column "Agent Name" contains any strings. If there is none, it will input "IBM Director" into the EventType column.
Once it has looped through the agent names, the code will then loop through the Details column and input into EventTypes based on what is displayed within the string.
... -
How do I solve this run-time error 3021?
Hi all,
I have managed to sort out the data to be used in the final table. However, I am having trouble transferring the data from each of their own tables into the final table.
Each time I run my code I receive "run-time error 3021: No current record."
It seems that only my timestamp is being added properly, but the error pops up and highlights the first "rstInsert.Edit " of my code.
... -
Ok, never mind! I found my problem! I forgot to change the name of the field in the If statementLeave a comment:
-
Ok, new plan. I'm thinking of extracting both the string and the "yes" or "no" into another table.
...Code:Private Sub Command8_Click() Dim dbs As DAO.Database Dim rst As DAO.Recordset Dim rstInsert As DAO.Recordset Set dbs = CurrentDb Set rst = dbs.OpenRecordset("Table2", dbOpenDynaset) Set rstInsert = dbs.OpenRecordset("Acknowledgement",Leave a comment:
-
shawnnnnnnn started a topic How to insert text into a new field based on the positioning of a string?in AccessHow to insert text into a new field based on the positioning of a string?
Hi all,
I am required to write a code to check the positioning of a text in a string.
E.g.
"~[Admin]~xxxxxxxxx~ACK~ " = Acknowledged
"~xxxxxxxxx ~[Admin]~ACK~" = Not Acknowledged
"~xxxxxxxxx~ACK ~[Admin]~" = Not Acknowledged
As you can see, as long as the string starts with "~[Admin]~", it is counted as acknowledged. If the "~[Admin]~"... -
I just solved my problem using the FileSystemObjec t method. All's well now :)Leave a comment:
-
When I import the file, some of the records get rearranged.
The records that are missing ended up at the bottom of the table.
When I use the sort from smallest to largest function on my ID field, it rearranges it properly. But my delete code made an error in the deletion.
It deleted records number 4028 to 4032. These are not supposed to be deleted as it is a normal event because this event consists of 6 lines...Leave a comment:
-
Here's my Access file, if it helps....Leave a comment:
No activity results to display
Leave a comment: