Can't enter data into query

[billheath]

I am trying to run a query which shows the values of all products from yesterday and lets me enter new data for today.

SELECT [Daily Value].Date, [Daily Value].Symbol, [Daily Value].[Current Value], [Daily Value_1].[Current Value] AS Yesterday, [Daily Value].[current value]-[yesterday] AS Change
FROM [Daily Value] AS [Daily Value_1] INNER JOIN [Daily Value] ON [Daily Value_1].Symbol = [Daily Value].Symbol
WHERE ((([Daily Value].Date)=[Date?]) AND (([Daily Value_1].Date)=[Daily Value].[Date]-1));

I enter today's date. All of the pertinent product symbols come up with the correct pricing. However, I can't enter any new data. I have to manually enter the new prices into the underlying table.

I suspect this has to do with selecting yesterday's date with a join from the same table. Hope this makes sense!! Thanks for your help. I have reviewed every post on queries in this forum. (And yes, I know that we shouldn't use the word "Date" as a field name. I inherited that one. However, if you think it would help, I can change that.)

 

[Andrzejek]

You may find some hint here: Why is my query not updateable? (I hope)


---- Andy

There is a great need for a sarcasm font.

 

[billheath]

I have tried everything at the site you mentioned. No luck. I think it might be that I am evaluating the date to arrive at yesterday's price.

 

[MajP]

It has a GROUP BY clause. A Totals query is always read-only.

It has a TRANSFORM clause. A Crosstab query is always read-only.

It uses First(), Sum(), Max(), Count(), etc. in the SELECT clause. Queries that aggregate records are read-only.

It contains a DISTINCT predicate. Set Unique Values to No in the query's Properties.

It involves a UNION. Union queries are always read-only.

It has a subquery in the SELECT clause. Uncheck the Show box under your subquery, or use a domain aggregation function instead.

It uses JOINs of different directions on multiple tables in the FROM clause. Remove some tables.

The fields in a JOIN are not indexed correctly: there is no primary key or unique index on the JOINed fields.

The query's Recordset Type property is Snapshot. Set Recordset Type to "Dynaset" in the query's Properties.

The query is based on another query that is read-only (stacked query.)

Your permissions are read-only (Access security.)

The database is opened read-only, or the file attributes are read-only, or the database is on read-only media (e.g. CD-ROM, network drive without write privileges.)

The query calls a VBA function, but the database is not in a trusted location so the code cannot run. (See the yellow box at the top of this Access 2007 page.)

The fields that the query outputs are Calcluated fields (Access 2010.)

Is symbol a PK? If not, what if you remove the inner Join and change the where to
WHERE [Daily Value].Date)=[Date?]) AND [Daily Value_1].Date)=[Daily Value].[Date]-1) and [daily value].symbol = [daily_value1.Symbo]

 

[billheath]

Thanks MajP, No, symbol is not a PK. There is an auto primary key but I didn't think it was necessary to show it.
I tried your inner join. However, same results. I still cannot update the fields in the query.
If this would help, I only need to update the current day's prices. The query will not allow me to enter anything.

 

[MajP]

If you have an autoID, did you try to join on that? I believe the issue is that your innerjoin is on a field that is not indexed.
The fields in a JOIN are not indexed correctly: there is no primary key or unique index on the JOINed fields
At least index the symbol field.

 

[billheath]

I think you are right. I may have to redesign the table. the PK is different for the 2 dates. Thanks

 

[MajP]

Sorry, do not know what I was thinking. Of course the PKs would be different since you are grabbing the next day, and you cannot index the symbol field since it is not unique. Are you building a form or are you trying to edit directly in the query? If you are building a form you could use the query for visibility of all information, but have features to allow you to update such as running update queries from unbound boxes, or maybe a subform based on an updateable query. Or your form could be based on an updateable query, but you pull in yesterdays information using a dlookup. This would even work on a continuous form.

 

[billheath]

Thanks. Great ideas! I'll get to work! I think building a form based on the table to add the new info but looking up yesterday with dlookup would work.

 

[MajP]

The other thing is you could build functions in the query to return yesterdays information, and then the query should be updateable
[Daily Value_1].[Current Value] AS Yesterday, [Daily Value].[current value]-[yesterday] AS Change. Although you could write this using dlookup right in the query it might be cleaner to make a UDF. Once you write this once you can use it in a query or form by passing in the symbol and the date. Below is untested.

Select ... GetYesterday([date],[Symbol]) as YesterdayValue from [Daily Value]

Public Function GetYesterday(theDate as variant, Symbol as variant) as variant
 dim nextDay as string 
 dim strWhere as string
 if not isnull(theDate) and not isnull(Symbol) then
    nextDay = Format(theDate-1, "dd/MM/YYYY")
    nextDay = "#" & nextDay & "#"
    strWhere = "Symbol = '" & Symbol & "' AND [Date] = " & nextDay
    GetYesterday = dlookup("[Current Value]","[Daily Value]",strWhere)
 end if
end function

 

[billheath]

Thanks, MajP, I've been out of town and just picked this up again yesterday. I had been toying with the dlookup idea before I had a chance to try your latest post. However, I used the function as the control source directly. =DLookUp("[Current Value]","[Daily Value]","Symbol = '" & [Symbol] & " ' AND [Date] = " & "#" & [yes_Date] & "#")
This returns 1/3 of the results correctly. The rest are blank. I'm not sure what the difference is. Is my Dlookup formula written wrong?
Mayby, I should start over with the code example you wrote. What do you think?
Thanks, again

 

[MajP]

The user defined function is nice because they are much easier to debug and you can easily reuse them. So the function could then be used in a query or on a form/report in a calculated control. If it is returning some correct values then I would assume your dlookup is correct.
One thing is I only need the # symbols if passing literals. Since you are comparing to a field value or control I think you can do away with them
...AND [Date] = " & [yes_Date])

Another thing that often happens is that the dates that you see are always a formatted representation of the true date time value stored in the db.
This time and date
1/25/2018 11:30:52 AM
is stored in the db as
43125.4798726852
Where the integer portion represents the date and the decimal represents the time. However based on the formatting applied you may see
1/25/2018 or 1 January 2018 or 1/25/18 11:30 AM

I do not know what yes_date is since you did not discuss that prior. But what often happens is that you may have a time value with your date based on how it is entered, but it is may not be shown. An example would be a time stamp set to =Now(), but you format that field to short date. You would have a time portion and not show it.
So #1/25/2018# will equal 43125.0 but not 43125.4798726852.
So this is a guess since you are getting some records but not others.
int([Date]) = " & int([yes_Date])
The int removes the time portion since it converts 43125.4798726852 to 43125 which formatted would be 1/25/2018

 

[billheath]

Weirdest thing. Just noticed an input mask ". When I delete it, the other values appear and the ones that were working do not. The quotation mark reappears. When I delete it again, the result goes back to the way it was before. The " in the input mask property never disappears. I don't understand where it came from or what effect it has.

 

[MajP]

Not sure I understand what you are saying. Can you post a couple screen captures.