Default value not showing in query

[stevekos07]

I have created a table with a default Day/Time value as Date(). It shows on the datasheet view of the table, but when I create a select query from that and another table, the date field is empty.

How do I set a default value in SQL?

SQL:

SELECT tblClients.ClientID, tblClientMoodHistory.Mood, tblClientMoodHistory.Notes
FROM tblClients
LEFT JOIN tblClientMoodHistory
ON tblClients.ClientID = tblClientMoodHistory.ClientID;

 

[MarkK]

Does that query allow you to add rows? If not, that is why you don't see the defaults.

 

[stevekos07]

Does that query allow you to add rows? If not, that is why you don't see the defaults.

Yes, I can add records, it is just the default value in the table field that is not populating into all records. I bring up the datasheet from the query and the "Mood" field is empty. I can edit the field in each record, but I want all records to be populated with the default value when the form opens.

This table is to record the overall client's sense of wellbeing when we call them. We call most clients every day, and the default is that they are ok. So we want the "mood" field to automatically display "yes". I have set the default value on that field as "Yes" but it doesn't populate all records. I tried a Boolean field first, but that didn't work so I tried a text field. I know I am missing something basic but I just can't work out what to do.

 

[MarkK]

One thing is that setting the DefaultValue property of a field only has an effect on new rows. Existing rows are not affected by a changed DefaultValue.

To cause a string field to take a value of "Yes" by default, put the table in design view, select the field whose default value you want to set, then type...

"Yes"

...into the DefaultValue property of the field, and save the table.

 

[stevekos07]

One thing is that setting the DefaultValue property of a field only has an effect on new rows. Existing rows are not affected by a changed DefaultValue.

To cause a string field to take a value of "Yes" by default, put the table in design view, select the field whose default value you want to set, then type...

"Yes"

...into the DefaultValue property of the field, and save the table.

Did that without success. I have another Date/Time field which is set in the query as CallDate: Date(). This works, but if I try that in the other field it sets the value as a non-editable value.

 

[MarkK]

Maybe you can post a database that demonstrates the problem, or show exactly what you are using as the default value. Demonstrate to us a way that we can reproduce the problem, otherwise troubleshooting it--without being able to cause it--is impossible.

What is the exact field type you are using? What is the exact text you assign to the default value property? What are the exact symptoms of the failure? Etc...

hth

 

[stevekos07]

Maybe you can post a database that demonstrates the problem, or show exactly what you are using as the default value. Demonstrate to us a way that we can reproduce the problem, otherwise troubleshooting it--without being able to cause it--is impossible.

What is the exact field type you are using? What is the exact text you assign to the default value property? What are the exact symptoms of the failure? Etc...

hth

Yes, good idea. I will use a test database to try to analyse the problem in stages. I will post a demo database with the problem if I can't solve it that way.