Date field not showing in a form that has been created using a query

[mismag]

Hi All,



I have created a form using a query and there is text box field with a date picker.
But when i select a date from the date picker, nothing is showing in the text box.

Any help is greatly appreciated.

TIA,
Mismag

[June7]

Post the query SQL statement. Or provide db for analysis. Follow instructions at bottom of my post.

[Micron]

You have enabled but locked the control?

[mismag]

Hi June7,

Thank you for the details to attach a database.
Attached is my database without any data in it.
Please let me know where I am doing wrong.

Thanks,
Mismag

[mismag]

The control is not locked Micron.

You have enabled but locked the control?

[June7]

Usual arrangement is one form does entry/edit for one table. You are including related tables in main and sub forms RecordSource with INNER join. Then you are binding controls to fields from various tables. Allow edits to those related records will corrupt data.

Start by binding each form to a table.

Table relationships don't really make sense to me. You link product to contact to dealership. Shouldn't product be associated with dealership? Contacts are employees of dealership - surely employees can change but dealership and its products would still be associated.

[mismag]

June7,

I am very new to databases and Access. I am learning and working on the project at the same time.

Table relationships don't really make sense to me. You link product to contact to dealership. Shouldn't product be associated with dealership? Contacts are employees of dealership - surely employees can change but dealership and its products would still be associated.

This makes sense. I changed the relationship of Products table to Dealership table.


But my problem of the Salesmonth is still the same.

Thanks,
Mismag

[mismag]

June7,

I have a followup question regarding the database.

In my contacts table there are two types(Title) of managers (Finance Managers & Sales Managers).
Finance Managers sell the products in the products table.
How do i connect them?

Thanks,
Mismag

[June7]

Actually, I am not sure even that is correct. You need to clearly identify data entities and relationships. What are the products - cars? What are the attributes of each car - serial number? Serial numbers are unique and would be associated with one dealership and each dealership has many cars - a 1-to-many relationship. Unlike if you only had make and model info. Multiple dealerships could have same make/model and many cars - this would be many-to-many relationship. Many-to-many requires a 'junction' table to associate records.

So which tables do you want to do data input into with this form/subform arrangement? Which table is the 'parent' (main form) and which is dependent (subform)?

Retrieving the dealership finance manager depends on how dealership/product/sales are related.

[mismag]

June7,

What are the products - cars?

Yes. But there are some other products that a finance manager tries to sell along with a car, like Warranty, Dent, WearCare, etc.
We don't have serial numbers of car.
All we get information from dealer is number of deals a finance manager made in a month.

Here is the tricky part.

A finance manager does 3 types of car deals, finance deals, Lease deals and cash deals.
We are not concerned about cash deals.

And not all the other products are sold (the FM try to sell) with each car deal.
See the attachment, you might get an idea.
Note that the total deals of a finance manager is not the sum of all the product's deals.
Hopefully you will get a clear picture.

Thanks a lot for all your efforts.
I am really thankful to you and all others who take time to help novice candidates like me.



Mismag.

[June7]

Is that worksheet showing products?

So I can envision at least 3 tables:

tblDealerships - this could have fields for FinanceMgr and SalesMgr info or you can have tblContacts that will have at least 2 records associated to each dealership

tblProducts
ProductID_PK
ProductName - I see 10 items in the worksheet image

tblSales - this would be a junction table that associates dealerships with products sold
SalesID
SalesDate - a full date, not just month
DealerID_FK
ProductID_FK - the worksheet shows 7 products with data so there would be 7 records.
Qty
Amount

Do you really need to associate Finance Manager directly with the Sales in historical info? So if the FM is replaced, do you need to be able to pull up the FM in place at the time of the sales?

I am less clear on how Goals should be associated. You want to set goals by product for each dealership? This means another table that associates products and dealerships. I don't think you need two goals tables.

[mismag]

tblSales - this would be a junction table that associates dealerships with products sold
SalesID
SalesDate - a full date, not just month
DealerID_FK
ProductID_FK - the worksheet shows 7 products with data so there would be 7 records.
Qty
Amount

I have a concern regarding this table. If you see the previous image, there is a row for Totals. Here the total for Deals is entered manually, this is not the sum of all the deals of products.
Penetration is a calculated field per product which is (Deals per Product/Total Deals)*100.
I am attaching an excel file of the previous image, so you know what I am trying to say here.

I am not sure in which table the TotalDeals field should be in? Because the calculation should be per product but the user enters it only one time per finance manager.

Do you really need to associate Finance Manager directly with the Sales in historical info? So if the FM is replaced, do you need to be able to pull up the FM in place at the time of the sales?

Yes

You want to set goals by product for each dealership?

Yes.

Thanks,
Mismag

[June7]

The Total figure in the image (which is the same as the uploaded file) is the sum of the items listed. So I do not understand why this total cannot be calculated instead of manual input.

However, if you must enter it, then perhaps you need tblSales and tblSalesDetails.

[mismag]

The Total figure in the image (which is the same as the uploaded file) is the sum of the items listed. So I do not understand why this total cannot be calculated instead of manual input.

However, if you must enter it, then perhaps you need tblSales and tblSalesDetails.

Sorry for the confusion....The total under the Deals column is not equal to the sum of all the deals per product (Attached the specific column).
Let me try adding 2 different tables for sales.

Thanks a lot June7.

Thanks,
Mismag

[June7]

I was only looking at the Gross column.

Is it a coincidence that total Deals equals VTR/Etch?