Ms access query by populating data base on a column (1 Viewer)

WIL

New member
Local time
Tomorrow, 07:37
Joined
Oct 14, 2019
Messages
9
Hi all I have A table like this

No |Payee |SLaccoun |Debit |Credit |CKCode |Date
1 |ARCEGA |Expenses |50 |0 |0 |10/4/2019
1 |ARCEGA |Remittance |0 |5 |0 |10/4/2019
1 |ARCEGA |Cash |0 |45 |1 |10/13/2019
1 |ARDIENTE |Expenses |50 |0 |0 |10/4/2019
1 |ARDIENTE |Cash |0 |50 |2 |10/12/2019

Now I wanted to make a Query inorder to generate another table like this
No |Payee |SLaccoun |Debit |Credit |CKCode |Date |Fdate
1 |ARCEGA |Expenses |50 |0 |0 |10/4/2019 |10/13/2019
1 |ARCEGA |Remittance |0 |5 |0 |10/4/2019 |10/13/2019
1 |ARCEGA |Cash |0 |45 |1 |10/13/2019 |10/13/2019
1 |ARDIENTE |Expenses |50 |0 |0 |10/4/2019 |10/12/2019
1 |ARDIENTE |Cash |0 |50 |2 |10/12/2019 |10/12/2019

Ive been Trying a DlookupFunction but it ends up to 10/13/2019
fDate: DLookUp("DATE","DV","CKcode > 0")
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 07:37
Joined
May 7, 2009
Messages
19,169
fDate: DLookUp("DATE","DV","CKcode > 0 And Payee='" & [Payee] & "'")
 

theDBguy

I’m here to help
Staff member
Local time
Today, 16:37
Joined
Oct 29, 2018
Messages
21,358
Hi. Welcome to AWF! Maybe you could try DMax() too.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 07:37
Joined
May 7, 2009
Messages
19,169
you're welcome.
if the date is always greater than the previous 2, you might want to involve the date
also:

fDate: DLookUp("DATE","DV","CKcode > 0 And Payee='" & [Payee] & "' And [date] >= " & Format([date], "\#mm\/dd\/yyyy\#"))
 

WIL

New member
Local time
Tomorrow, 07:37
Joined
Oct 14, 2019
Messages
9
you're welcome.
if the date is always greater than the previous 2, you might want to involve the date
also:

fDate: DLookUp("DATE","DV","CKcode > 0 And Payee='" & [Payee] & "' And [date] >= " & Format([date], "\#mm\/dd\/yyyy\#"))

Seems Like the Data will store it as Text and if I convert it to Cdate(DLookUp("DATE","DV","CKcode > 0 And Payee='" & [Payee] & "' And [date] >= " & Format([date], "\#mm\/dd\/yyyy\#")))

Some Blanks returns to Error

And if I add up IIF(ISNULL([DATA]),NULL,DATA) It will work but it slows down if I filter the coloumn by dates. Any Suggestion or work around that I filter it much more faster?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 07:37
Joined
May 7, 2009
Messages
19,169
fDate: DLookUp("DATE","DV","CKcode > 0 And Payee='" & [Payee] & "' And Nz([date], 1) >= " & Format([date], "\#mm\/dd\/yyyy\#"))
 

WIL

New member
Local time
Tomorrow, 07:37
Joined
Oct 14, 2019
Messages
9
fDate: DLookUp("DATE","DV","CKcode > 0 And Payee='" & [Payee] & "' And Nz([date], 1) >= " & Format([date], "\#mm\/dd\/yyyy\#"))

This is great... But is there any work around? I tried to export this table to excel Using Ado VBA IN EXCEL

SQL = "SELECT * FROM TBLQUERY

seems like the recordset does not capture data in the calculated field
 

WIL

New member
Local time
Tomorrow, 07:37
Joined
Oct 14, 2019
Messages
9
Using Inner Select Query Solved This.

I made 3 table Queries

Table1
No
Payee
SlAccoun
Debit
Credit
CkCode

Table2
No
Payee
Date

Table3
No
Payee
SlAccoun
Debit
Credit
CkCode
Date
Fdate: (Select DATE From Table2 where Table1.No = Table2.NO and Table1.Payee=Table2.Payee)


Or Under SQL
Code:
SELECT Table1.No, Table1.Payee, Table1.SlAccoun, Table1.Debit, Table1.Credit, Table1.CkCode, Table1.Date,(Select DATE From Table2 where Table1.No = Table2.NO and Table1.Payee=Table2.Payee) as Fdate
FROM Table1;

I Think THis Will Solve It
 

theDBguy

I’m here to help
Staff member
Local time
Today, 16:37
Joined
Oct 29, 2018
Messages
21,358
Hi. Glad to hear you got it sorted out. Good luck with your project.
 

Users who are viewing this thread

Top Bottom