Query non-normalized data?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Pardon me, I think I'll be babbling on this question...

I have a couple of fields, VendorChqNum and VendorChqDate, which are in a
1:M relationship with a field InvoiceNum. At design time I failed to catch
this, so I am presently filling up the Invoices table with multiple instances
of VendorChqNum and VendorChqDate, instead of from a lookup table.

When I'm entering the VendorChqDate field I'd like to:
1) check for null in the previous field, VendorChqNum
2) if not null, I'd like to do a query on the table and automatically fill
in the date, VendorChqDate, that is always associated with that VendorChqNum.


I am being thrown off by the knowledge that the query will return many rows
that match VendorChqNum, and that occasionally there may be a typo where the
date is wrong. However, I would be satisfied with a fill-in from the first
instance of that ChqNum...

Can anyone help with the code for this?
 
Ricter,

Try this, on the Enter event of VendorChqDate...
Dim DateToUse As Variant
If IsNull(Me.VendorChqDate) And Not IsNull(Me.VendorChqNum) Then
DateToUse = DLookup("[VendorChqDate]","YourTable","[VendorChqNum]="
& Me.VendorChqNum)
If IsNull(DateToUse) Then
' new ChqNum
Else
Me.VendorChqDate = DateToUse
End If
End If
(assumes VendorChqNum is number data type)
 
Steve,

VendorChqNum is actually a text type field...


Steve Schapel said:
Ricter,

Try this, on the Enter event of VendorChqDate...
Dim DateToUse As Variant
If IsNull(Me.VendorChqDate) And Not IsNull(Me.VendorChqNum) Then
DateToUse = DLookup("[VendorChqDate]","YourTable","[VendorChqNum]="
& Me.VendorChqNum)
If IsNull(DateToUse) Then
' new ChqNum
Else
Me.VendorChqDate = DateToUse
End If
End If
(assumes VendorChqNum is number data type)

--
Steve Schapel, Microsoft Access MVP
Pardon me, I think I'll be babbling on this question...

I have a couple of fields, VendorChqNum and VendorChqDate, which are in a
1:M relationship with a field InvoiceNum. At design time I failed to catch
this, so I am presently filling up the Invoices table with multiple instances
of VendorChqNum and VendorChqDate, instead of from a lookup table.

When I'm entering the VendorChqDate field I'd like to:
1) check for null in the previous field, VendorChqNum
2) if not null, I'd like to do a query on the table and automatically fill
in the date, VendorChqDate, that is always associated with that VendorChqNum.


I am being thrown off by the knowledge that the query will return many rows
that match VendorChqNum, and that occasionally there may be a typo where the
date is wrong. However, I would be satisfied with a fill-in from the first
instance of that ChqNum...

Can anyone help with the code for this?
Click to expand...
Click to expand...
 
DateToUse = DLookup("[VendorChqDate]","YourTable","[VendorChqNum]='" &
Me.VendorChqNum & "'")
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

query wierdness 1
database normalization 6
Adding a Date data-type field with a Make-Table query 3
date selection in vba vs query designer 5
Run Update Query From Form 0
Data Mismatch in Excel 2010 4
move data from a non-normalized table to normalized tables 1
Only show field with date 2

Back
Top