True, I didn't really want to use the requery method... Using cpu cycles.. I didn't know we could listen to emails.. And prompt access to action it. ..could this be an add on I need to apply to Outlook?... Anyone have experience with doing this?
Delimited Text to 10 seperate columns (1 Viewer)
- Thread starter memaxt
- Start date
Ok, yes you can handle incoming mail and act accordingly and yes I was able to do it from within Access but there are two downsides to this approach:
1. Outlook needs to be running.
2. If multiple e-mails are received at around about the same time, you may miss some messages.
So I think what you're doing (i.e. linking to the folder and processing it every x seconds) is fine. But here are some refinements to the idea:
1. Create a table that will hold the date/time stamp of the most recent e-mail read.
2. Create a query to return only new e-mails. The criteria will be greater than the date/time stamp from step 1.
3. Before processing the emails in step 2, get the most recent date/time stamp using the DMax() function or a Max query. Save it somewhere.
4. Process the e-mails in step 2 and update the table in step 1 with the max date/time from step 3.
Essentially, the query will only return those e-mails that haven't been processed and all of this should happen within the 30 seconds timeframe you set.
1. Outlook needs to be running.
2. If multiple e-mails are received at around about the same time, you may miss some messages.
So I think what you're doing (i.e. linking to the folder and processing it every x seconds) is fine. But here are some refinements to the idea:
1. Create a table that will hold the date/time stamp of the most recent e-mail read.
2. Create a query to return only new e-mails. The criteria will be greater than the date/time stamp from step 1.
3. Before processing the emails in step 2, get the most recent date/time stamp using the DMax() function or a Max query. Save it somewhere.
4. Process the e-mails in step 2 and update the table in step 1 with the max date/time from step 3.
Essentially, the query will only return those e-mails that haven't been processed and all of this should happen within the 30 seconds timeframe you set.
Many thanks for your Outlook advice, I have now applied it to my database successfully!!! 
But i'm still struggling with Parsing all those delimited text, even though you provided me with that I still have no clue on where to start. my coding head has completely gone!
Do you have a sample database at all? that contains atleast 10 delimited text to 10 columns?
Apologies for being so useless!
But i'm still struggling with Parsing all those delimited text, even though you provided me with that I still have no clue on where to start. my coding head has completely gone!
Do you have a sample database at all? that contains atleast 10 delimited text to 10 columns?
Apologies for being so useless!
Goodie!
No I don't. To start with, place the code in a new Module (not a Class Module, just a Module), Debug > Compile it, then us it as explained in your query. You said you have linked to the file so you should be able to create a query based on the linked table and call it in the query.
No I don't. To start with, place the code in a new Module (not a Class Module, just a Module), Debug > Compile it, then us it as explained in your query. You said you have linked to the file so you should be able to create a query based on the linked table and call it in the query.
I have a query called "Main" which contains the following column:
"Contents"
within Contents it has the following strings: ID#NAME#AGE#DOB#EMAIL
Ok so I copied the function into a Module (None Class)
Then
Created a form button and added:
Private Sub Command0_Click()
GetParts(0, "ID,Name,AGE,DOB,Email")
End Sub
when I click the button I get "Syntex Error" - Expected =
Any ideas?
"Contents"
within Contents it has the following strings: ID#NAME#AGE#DOB#EMAIL
Ok so I copied the function into a Module (None Class)
Then
Created a form button and added:
Private Sub Command0_Click()
GetParts(0, "ID,Name,AGE,DOB,Email")
End Sub
when I click the button I get "Syntex Error" - Expected =
Any ideas?
So In my "Main" Query
I will just paste GetParts(0, [FieldName]) into the first field???
I pasted the following in the first field
ID: GetParts(0,[Contents])
I get "Run time error 9 - Subscript out of range"
Debug highlights:
If UBound(varSplit) > 0 Then
For x = 1 To UBound(varSplit)
varParts(x) = varSplit(x)
After adding all 1-9 fields in query i no longer get the error above - but i also dont get any data in my query.
I've added all:
GetParts(0,[Contents])
GetParts(1,[Contents])
GetParts(2,[Contents])
-
GetParts(9,[Contents])
i've also tried:
Expr1: GetParts(0,[id])
Expr2: GetParts(1,[name])
Expr3: GetParts(2,[dob])
No data is displayed
ID: GetParts(0,[Contents])
I get "Run time error 9 - Subscript out of range"
Debug highlights:
If UBound(varSplit) > 0 Then
For x = 1 To UBound(varSplit)
varParts(x) = varSplit(x)
After adding all 1-9 fields in query i no longer get the error above - but i also dont get any data in my query.
I've added all:
GetParts(0,[Contents])
GetParts(1,[Contents])
GetParts(2,[Contents])
-
GetParts(9,[Contents])
i've also tried:
Expr1: GetParts(0,[id])
Expr2: GetParts(1,[name])
Expr3: GetParts(2,[dob])
No data is displayed
Last edited:
Why would it work when it threw an error in the first place
The example you showed is in 10 parts, but your string is returning more than 10 parts.
Private varParts(1 To 9) As Variant
Why would it work when it threw an error in the first place
The example you showed is in 10 parts, but your string is returning more than 10 parts.
Private varParts(1 To 9) As Variant
I've added all 10 - No error comes up / still no data displaying
- So i've created a query in design view.
- Added the table to the query that contains Delimited Text
- The field that contains the delimited text is call "Contents"
- So if i add "Contents" to the first field of the query, the query only displayed one field that contains on delimited text.
Now if i remove "Contents" from the first field of the query anhd replace it with
"Expr1: GetParts(0,[Contents])"
Then add the rest to each field
"Expr1: GetParts(1,[Contents])"
"Expr1: GetParts(2,[Contents])"
"Expr1: GetParts(3,[Contents])"
"Expr1: GetParts(4,[Contents])"
"Expr1: GetParts(5,[Contents])"
"Expr1: GetParts(6,[Contents])"
"Expr1: GetParts(7,[Contents])"
"Expr1: GetParts(8,[Contents])"
"Expr1: GetParts(9,[Contents])"
It displays no data
------------------>
Have a look at the following text, how many parts do you think it will split it into?
Code:
1068#reman#(0161) 4957#Hospice[COLOR="blue"]##[/COLOR]Gill Acreman#87I2567E#Stockport#St Ann's Hospice#St Ann's Road North#Heald Green#SK8 3SZ[COLOR="blue"]##[/COLOR]ST/N D Foulkes#79i3237e#29-08-2014#No#No#N/A[COLOR="blue"]##[/COLOR]No[COLOR="blue"]##[/COLOR]Hospice #Administration error[COLOR="blue"]###[/COLOR]Other please specify[COLOR="blue"]##[/COLOR]Oxynorm#5mg / 5mls#Solution[COLOR="blue"]##[/COLOR]25mg# breakthrough dose at 08h30. *Nurse did not administer regular dose at same time. *This was subsequently missed.#[COLOR="Blue"]######################[/COLOR]Green 1-4#Yes#given advice only#No harm##Nurse did not follow procedure of writing on board that medication was due
#As patient had received breakthrough dose, patient did not have any additional pain#Incident was not realised until 14h00. *Further breakthrough given.
#Nurse made aware of error and reminded of procedure[COLOR="blue"]##[/COLOR]Yes - All staff advised[COLOR="blue"]###[/COLOR]gac.uk#I agree#My apologies I thought it would be best to provide a simple table as an example
such as
ID#NAME#AGE#DOB#EMAIL
where I could then adapt the same principle to the current test db
Apologies again, I really didnt intend to mislead you..All I do know is that i'm very thankful to both yourself and many others for the continuing guidance!
