Looking for match values of more complex "if's"

M

mfaqueiroz

Registered User.
Local time
Today, 15:16
Joined
Sep 30, 2015
Messages
125
Hi,

I'm comparing two tables, A and B. I want when Painel it's the same in both tables and the difference of the time it's less then one minute give me the value of field [human] that belong to B table, and writes this value in my A table.
I wrote the following code, but isn't working... ..could you please help me!
I'm stuck:banghead:

I really appreciate your dedication and time!



Private Sub OrdLigDesl()

Dim y As Integer
Dim x As Integer
Dim TempoLigDesl As Date
Dim TempoEvenLog As Date

Set dbs = DBEngine(0)(0)
Set A= dbs.OpenRecordset("A", DB_OPEN_TABLE)
Set B= dbs.OpenRecordset("B", DB_OPEN_TABLE)

contaA = DCount("[Painel]", "A")
contaB= DCount("[Painel]", "B")
x = 0
y = 0


A.MoveFirst


For y = 1 To contaA
TimeA= TimeValue(A.Fields(2).Value)
PainelA= A.Fields(5).Value


B.MoveFirst

For x = 1 To contaB

TempoB= TimeValue(B.Fields(1).Value)
PainelB = B.Fields(7).Value
Human = B.Fields(4).Value


If PainelA = PainelB And DateDiff("n", tempoA, tempoB) <= 1 Then

A.Edit
A.Fields(9).Value = Yes
A.Update



Else

End If
B.MoveNext

Next x
A.MoveNext
Next y


End Sub
 
This sounds like something that can be done via query. Can you please provide sample data to demonstrate what you want? Provide 2 sets of data:

1. Starting sample data from A & B. Be sure to include field names and enough data to cover all cases.

2. Expected results. Based on the sample data in 1, show what you expect to have as a result.
 
Sure,
1.Table B
Date Human Painel
01-01-2014 04:27:41 AB
01-01-2014 04:27:45 ABC
01-01-2014 08:57:47 AB
01-01-2014 08:57:47 ABD
01-01-2014 08:57:47 ASF
01-01-2014 08:57:47 AEF
01-01-2014 08:57:47 AQF
01-01-2014 08:57:47 AB
01-01-2014 08:57:47 ABC
01-01-2014 08:57:47 AB
01-01-2014 08:57:47 ABD
01-01-2014 08:57:47 ASF
01-01-2014 08:57:47 AEF
01-01-2014 08:57:47 AQF
01-01-2014 08:57:47 DD
01-01-2014 08:57:47 ASF
01-01-2014 08:57:47 DD
01-01-2014 08:57:47 AEF
01-01-2014 08:57:47 AQF
01-01-2014 08:57:47 DD
02-01-2014 08:57:51 Yes ASF
03-01-2014 15:27:05 AB
03-01-2014 15:27:05 ABC
03-01-2014 15:27:55 DD
03-01-2014 15:29:00 ABD
03-01-2014 23:56:34 Yes ASF
03-01-2014 23:57:20 AEF
04-01-2014 00:47:00 DD
02-01-2014 12:03:49 ASF
02-01-2014 12:05:27 AEF
02-01-2014 13:11:35 AEF
02-01-2014 13:12:22 ABD
02-01-2014 14:00:57 Yes ASF
02-01-2014 14:00:57 AEF
02-01-2014 14:00:57 AQF
02-01-2014 14:00:57 DD
02-01-2014 14:00:57 ASF


Table A:
Date Painel Human?
01-01-2014 08:58:34 DD
01-01-2014 08:58:34 AEF
01-01-2014 08:58:34 AQF
01-01-2014 08:58:34 DD
01-01-2014 08:58:34 ASF
01-01-2014 08:59:14 AB
01-01-2014 09:04:44 ABC
02-01-2014 06:33:37 DD
02-01-2014 06:33:37 ABD
02-01-2014 06:33:37 ASF
02-01-2014 06:33:37 AEF
02-01-2014 07:03:01 DD
02-01-2014 06:33:53 ASF
02-01-2014 06:34:27 AEF
02-01-2014 06:34:27 AEF
02-01-2014 06:34:27 ABD
02-01-2014 07:02:01 ASF
03-01-2014 07:40:35 AEF
03-01-2014 07:41:37 AQF
03-01-2014 15:27:05 AB
03-01-2014 15:27:05 ABC
03-01-2014 15:27:55 AB
03-01-2014 15:29:00 ABD
03-01-2014 23:56:34 ASF
03-01-2014 23:57:20 AEF
04-01-2014 00:47:00 AQF
02-01-2014 12:03:49 DD
02-01-2014 12:05:27 ASF
02-01-2014 13:11:35 AB
02-01-2014 13:12:22 ABC
02-01-2014 14:00:57 DD
02-01-2014 14:00:57 ABD
02-01-2014 14:00:57 ASF
02-01-2014 14:00:57 AEF
02-01-2014 14:00:57 DD
02-01-2014 14:00:57 ASF
02-01-2014 23:56:21 ASF
02-01-2014 23:57:22 AEF
03-01-2014 03:30:33 AQF


2. The results : Will search the values with less than 1 minute of difference and in the same painel and fill the human column for A table:

Date Painel Human?
01-01-2014 08:58:34 DD
01-01-2014 08:58:34 AEF
01-01-2014 08:58:34 AQF
01-01-2014 08:58:34 DD
01-01-2014 08:58:34ASF Yes
01-01-2014 08:59:14 AB
01-01-2014 09:04:44 ABC
02-01-2014 06:33:37 DD
02-01-2014 06:33:37 ABD
02-01-2014 06:33:37 ASF
02-01-2014 06:33:37 AEF
02-01-2014 07:03:01 DD
02-01-2014 06:33:53 ASF
02-01-2014 06:34:27 AEF
02-01-2014 06:34:27 AEF
02-01-2014 06:34:27 ABD
02-01-2014 07:02:01 ASF
03-01-2014 07:40:35 AEF
03-01-2014 07:41:37 AQF
03-01-2014 15:27:05 AB
03-01-2014 15:27:05 ABC
03-01-2014 15:27:55 AB
03-01-2014 15:29:00 ABD
03-01-2014 23:56:34ASF yes
03-01-2014 23:57:20 AEF
04-01-2014 00:47:00 AQF
02-01-2014 12:03:49 DD
02-01-2014 12:05:27 ASF
02-01-2014 13:11:35 AB
02-01-2014 13:12:22 ABC
02-01-2014 14:00:57 DD
02-01-2014 14:00:57 ABD
02-01-2014 14:00:57ASF yes
02-01-2014 14:00:57 AEF
02-01-2014 14:00:57 DD
02-01-2014 14:00:57 ASF
02-01-2014 23:56:21 ASF
02-01-2014 23:57:22 AEF
03-01-2014 03:30:33 AQF
 
Last edited:
FYI post 3 was moderated, I'm posting to trigger email notification for plog. I deleted the duplicate posts that were also moderated.
 
I'm sorry, I though that was an error...i'll not repeat again
 
No problem. The first post was moderated, so you probably couldn't see it, and assumed it hadn't gone through. Not sure if the forum tells you it is moderating the post (and I'm not sure why yours was, as there were no links).
 
As an aside, concerning : Set A= dbs.OpenRecordset("A", DB_OPEN_TABLE)

1. Not a good idea to specify dbOpenTable if you ever split your db. dbOpenDynaset is better (unless you use and intend to keep using the .Seek method)

2. This parameter is normally dbOpenTable, not DB_OPEN_TABLE. Did you make up that parameter and give it a value yourself?

3. Do you have Option Explicit at the top of each of your modules? You should!
 
Thank you the corrections:)
I've change to the dbOpenDynaset
and define the variable with the of option explicit...but the code isn't doing what I want, anny suggestion for that?

thank you!
 
Your data is a little screwy:

1. In B the field Human has values like 'AB', 'ABC', 'ASF', but in A the Human? field contains nulls and 'Yes' values and that's it.

2. In B the field Paniel has only nulls and 'ASF' values. In A, its values like 'AB', 'ABC', 'ASF'.

Can you repost your data with the proper values/columns?
 
Sorry,
The column Human only is fill with "Yes"
the letters are correspondent to the Painel.
I've tried to edit to be more easy to read but everything appears all united... how can i copy tables for here?
 
Last edited:
You might be better served using a comma as a delimiter

Date, Painel, Human
01-01-2014 08:58:34, DD,
 
You must log in or register to reply here.

Similar threads

K
How can I add this code to the storage location of my file before scanning and then convert it to a PDF file?
2
Replies
25
Views
1,518
The_Doc_Man
The_Doc_Man
U
Export blob OLE Object Field Item instead of Attachment Field
Replies
9
Views
852
jdraw
jdraw
jwcolby54
Building a table for my book
Replies
15
Views
533
jwcolby54
jwcolby54
M
Looking for Faster way to update Table - boolean field
2 3
Replies
49
Views
2,973
cheekybuddha
cheekybuddha
S
Solved Boolean values in table controlled by list box
Replies
17
Views
1,027
Gasman
Gasman

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom