Help to check 2 tables by DCount in the same code (1 Viewer)

lookforsmt

Registered User.
Local time
Today, 19:06
Joined
Dec 26, 2011
Messages
672
HI! all

I have two tables tbl_A and tbl_B and form called frm_X which as combobox cbo_A with two details added: "Accept" and " Reject"

i want the Dcount function to check in tbl_A if the user selects cbo_A (Accept) and if the user selects in cbo_B (Reject) then it should check in tbl_B.

Below is my Dcount function. but i am not able to apply the combobox condition.

PHP:
If DCount("*", "[tbl_A]", "[chqbrcd] = '"& chqbrcd & "' and [Handover_date] = "& SQLDate(Handover_date)) = 0 Then

Any suggestions how to apply this same code when the user selects cbo_A or cbo_B
 

June7

AWF VIP
Local time
Today, 07:06
Joined
Mar 9, 2014
Messages
5,425
Use conditional statements to set variables. Concatenate variables to construct expression. Actually, a single combobox with the 2 choices may be better.

If DCount("*", cboTbl, "[chqbrcd] = '"&

Why do you have two tables to choose from anyway?
 
Last edited:

GinaWhipp

AWF VIP
Local time
Today, 11:06
Joined
Jun 21, 2011
Messages
5,901
Hmm, a litt;e concerned about our table set-up but that said... I'm not sure I understand why you are using a DCount() to check for a value. Why not a DLookup()? Perhaps explaining exactly what the business rules behind the tables and the DCount would us understand better...
 

lookforsmt

Registered User.
Local time
Today, 19:06
Joined
Dec 26, 2011
Messages
672
HI! i am open to use either way DCount or Dlookup as long as i get the result.

There is only one combobox on form with 2 choice " Accept" or "Reject"

If the user selects "Accept", then it should look in tbl_A and likewise if the user selects "Reject" it should look in tbl_B

i am looking to use the iff function so i dont need to create another form for user to capture chqbrcd in different forms.

Is the above possible, if yes than can anyone modify on my original code pls
Thanks
 

plog

Banishment Pending
Local time
Today, 10:06
Joined
May 11, 2011
Messages
11,613
First, it seems odd that you have 2 different tables to look into for this. Especially when those tables have the same fields. Smells like an improper table structure from here.

Second, make the table argument a variable and assign it prior to hitting the DCount:

Code:
str_Table ="[tbl_A]"
If cbo_B = True Then str_Table="[tbl_B]"

If DCount("*", str_Table, "[chqbrcd] = ...

Again, though, this probably shouldn't even have to happen if your data was properly structured with everything in one table.
 

June7

AWF VIP
Local time
Today, 07:06
Joined
Mar 9, 2014
Messages
5,425
So you changed from 2 to 1 combobox?

If you make the combobox multi-column with the actual table name in second column (can be hidden), won't even need to declare and set variable.

If DCount("*", cboTbl.Column(1), "[chqbrcd] =
 

lookforsmt

Registered User.
Local time
Today, 19:06
Joined
Dec 26, 2011
Messages
672
Hi! Thanks for looking into this. Reading the post, I want clarify again to all

User will select in cbo_A either Accept or Reject,
then enter chqbrcd which should look into tbl_A or tbl_B.

the condition is:
If the cbo_A field is Accept then look only in tab_A.
If the cbo_B field is Reject then look only in tab_B.
 

June7

AWF VIP
Local time
Today, 07:06
Joined
Mar 9, 2014
Messages
5,425
Now very confused. Why do you have 2 tables? Why have 2 comboboxes? What should happen if select Reject in cbo_A or Accept in cbo_B?

You have example code structure to use. Now modify for your situation.
 

lookforsmt

Registered User.
Local time
Today, 19:06
Joined
Dec 26, 2011
Messages
672
i have attached snapshot of my form where user will select combobox and then capture chqbrcd, based on the combobox selection the DCount should check in tbl_A or tbl_B
 

Attachments

  • DCount_1.png
    DCount_1.png
    48.4 KB · Views: 34

June7

AWF VIP
Local time
Today, 07:06
Joined
Mar 9, 2014
Messages
5,425
Okay, did you try code suggested in post 6?
 

lookforsmt

Registered User.
Local time
Today, 19:06
Joined
Dec 26, 2011
Messages
672
hi! all

I scrapped the earlier method and changed the table structure too.
Below is the new code,, however i am now facing a new challenge in date.
It kind of reads the date format as mm/dd/yyyy when the date is 01/09/2018
(1st Sep-2018). But when i type in date as 09/01/2018 it accepts.
below is the code
Code:
If DCount("*", "[tbl_Temp_ICCS_envbrcd by LodgeDt]", "[chqbrcd_el] = '" & chqbrcd_e & "' And [pdc_dueDate] = #" & Format(Me.chqbrcdDate, "dd/mm/yyyy") & "# And chqbrcd_el = '" & Me.chqbrcd_e & "'") = 0 Then

any suggestions how i can change this code to accept the date as 01/09/2018 as 1st sept 2018 as date.
 

JHB

Have been here a while
Local time
Today, 16:06
Joined
Jun 17, 2012
Messages
7,732
Try the below, (not tested).
Code:
If DCount("*", "[tbl_Temp_ICCS_envbrcd by LodgeDt]", "[chqbrcd_el] = '" & chqbrcd_e & "' And [B][COLOR=Red]Format([pdc_dueDate],'dd/mm/yyyy')[/COLOR][/B] = #" & Format(Me.chqbrcdDate, "dd/mm/yyyy") & "# And chqbrcd_el = '" & Me.chqbrcd_e & "'") = 0 Then
 

lookforsmt

Registered User.
Local time
Today, 19:06
Joined
Dec 26, 2011
Messages
672
thanks JHB for your code. However that did not work. But i tried and changed in my original code the date format to "mm/dd/yyyy" and it worked.
I dont know the logic why, but as log as it has given me the result.

Thanks for your time & help anyways.
 

plog

Banishment Pending
Local time
Today, 10:06
Joined
May 11, 2011
Messages
11,613
First you had two tables with the same structure and now you have temporary table? Sounds like you traded one hack for another.

Can you complete the Relationship Tool, expand it so we can see all the tables and their fields, then take a screenshot and post it?
 

lookforsmt

Registered User.
Local time
Today, 19:06
Joined
Dec 26, 2011
Messages
672
Sure plog, i will do that, kindly give me some time.

thanks
 

Mark_

Longboard on the internet
Local time
Today, 08:06
Joined
Sep 12, 2017
Messages
2,111
thanks JHB for your code. However that did not work. But i tried and changed in my original code the date format to "mm/dd/yyyy" and it worked.
I dont know the logic why, but as log as it has given me the result.

Thanks for your time & help anyways.

Just to be clear, dates are stored as a number but are displayed by formatting that number into something that people understand.

1 Sep 18 isn't stored as dd/mm/yyyy but is stored as 43344. SQL, and ACCESS, expect you to pass a date to the back end formatted as MM/DD/YYYY. This is done because they were developed in the U.S. and the developers simply chose a format they were use to. They have kept that specific format to avoid ambiguity when passing dates.

For myself, I wish they'd have use YYYYMMDD for the default SQL "Date" format. Would have made it much easier to avoid issues of MM/DD VS DD/MM in formatting.
 

Users who are viewing this thread

Top Bottom