Displaying only the dates that exist for the specific customer (screen)

[noks]

Hi

Can you please help me with my code. I'm trying to display only the dates that are available in the history table fo the customer displayed in the screen & it's not working

Here's my code:

SELECT DISTINCT date FROM Sasol_campaign_history WHERE sasol_master_campaign.[store name]=sasol_campaign_history.[store name];

and i tried this as well

SELECT DISTINCT date FROM Sasol_campaign_history WHERE sasol_campaign_history.[store name] = [forms]![frmSasol_campaign]![store name];

it's in the row source

Please kindly help

Noks

 

[neileg]

Date is a reserved word in Access so you can get unexpected effects when you use it as a field name. Try changing your field name before you go any further.

 

[noks]

I've changed it to txtDate but now it displays it fo each and every customer even the ones that don't hav the history. Pls help

Noks

 

[neileg]

SELECT DISTINCT txtDate FROM Sasol_campaign_history WHERE sasol_campaign_history.[store name] = [forms]![frmSasol_campaign]![store name];

should work

OK, some more questions
1) For the above statement to work, you need a control on the form called store date. Are you sure that the control is called that and it's not simply the name of the underlying field? Note that the Access wizards give the controls the same name as the fields, even though this is a bad idea.
2) Similarly, txtDate needs to be the name of the field and not the name of the control on the form.
3) Is frmSasol_campaign a form and not a subform?

 

[noks]

SELECT DISTINCT txtDate FROM Sasol_campaign_history WHERE sasol_campaign_history.[store name] = [forms]![frmSasol_campaign]![store name];

should work

OK, some more questions
1) For the above statement to work, you need a control on the form called store date. Are you sure that the control is called that and it's not simply the name of the underlying field? Note that the Access wizards give the controls the same name as the fields, even though this is a bad idea.
2) Similarly, txtDate needs to be the name of the field and not the name of the control on the form.
3) Is frmSasol_campaign a form and not a subform?

1) The control source is [Store name] as well as the field
2) txtDate is the name of both the field and control source [my fields r bound 2 my table so their control source is the tabe name]
3) It's the form

Noks

 

[neileg]

As I suggested, having the control name the same as the field name is a bad idea. I always name my controls with a prefix that identifies the type, txt, or cbo or whatever.