Query will not display fields on form

[Design by Sue]

I am creating an append query based on a form. I have all of the info entered correctly but for some reason 2 of the fields will not show any value in the query results. The fields are a date field and a number field. Please I want to keep this as simple as possible without getting into a complete study of my database. Is there some reason that anyone knows of why some fields will not display in a query from a form? I also tried this as simple query and of the 5 fields on the query, 3 display but the 2 mentioned show no info.

 

[Gasman]

And you cannot even show the the query SQL?

 

[Design by Sue]

The only thing I can see that is different for these 2 fields is that the information is not saved in the database. The other fields are based on existing information in the tables. The 2 fields I am trying to access are only entered information that is not saved elsewhere. (hope that makes sense)

 

[Design by Sue]

This is the select query

SELECT [Forms]![Add by SOP FRM]![ComboSOPNo] AS SOPNumber, [Forms]![Add by SOP FRM]![SOPVersion] AS SOPVersion, [Forms]![Add by SOP FRM]![ComboEmpName] AS EmployeeNumber, [Forms]![Add by SOP FRM]![DateCompleted] AS DateCompleted, [Forms]![Add by SOP FRM]![TimeCompleted] AS TimeCompleted;

 

[Gasman]

Wow, I have never used a Select on form controls.
I will bow out.

 

[Design by Sue]

Wow, I have never used a Select on form controls.
I will bow out.

Figures I would need to do something "unheard" of - thanks anyway - anyone else got any idea

 

[The_Doc_Man]

Do you have warnings disabled when you do this? Because I would have sworn that you couldn't build a SELECT without a FROM. If you have somehow suppressed warnings then you aren't seeing what Access is trying to tell you.

But I have an even crazier question. You could write a SELECT query to drive a bound form, the [Add By SOP FRM] form. Then if you wanted, you could append that record somewhere else from the bound form. Why are you explicitly avoiding the parts of Access that makes it more powerful?

And finally, if those items were in a bound form, they are already in a table. Why would you append values already extant in another table? Gilding a lily? It somehow seems superfluous.

Even if this is a data entry form, it should be a bound form as well. I'm just confused by your attempt for something that should be near automatic unless you have left something out of your description.

 

[MajP]

anyone else got any idea

I am creating an append query based on a form

Something like this. Need to assign the correct data types, the correct names, and delimit correctly. Strings get single quotes ' and dates get #

dim strSql as string
dim SopNo as long
Dim SopVersion as String
Dim EmpNumber as long
Dim dateCompleted as string
dim timeCompleted as string

SopNo = [Forms]![Add by SOP FRM]![ComboSOPNo]
SopVersion = "'" & [Forms]![Add by SOP FRM]![SOPVersion] &"'"
EmpNumber = [Forms]![Add by SOP FRM]![ComboEmpName]
DateCompleted = "#" & format([Forms]![Add by SOP FRM]![DateCompleted], "mm/dd/yyyy") & "#"
TimeCompleted = "#" & format([Forms]![Add by SOP FRM]![TimeCompleted], "hh:mm:ss am/pm") & "#"

strSql = "insert into someTable (SopNo, SopVersion, EmpNumber, DateCompleted, TimeCompleted) "
strSql = strSql & " values (" & SOPNo & ", " & SOPversion" & ", " & EmpNumber & ", " & DateCompleted & ", " & timeCompleted & ")"

currentdb.execute Strsql

 

[LarryE]

This is the select query

SELECT [Forms]![Add by SOP FRM]![ComboSOPNo] AS SOPNumber, [Forms]![Add by SOP FRM]![SOPVersion] AS SOPVersion, [Forms]![Add by SOP FRM]![ComboEmpName] AS EmployeeNumber, [Forms]![Add by SOP FRM]![DateCompleted] AS DateCompleted, [Forms]![Add by SOP FRM]![TimeCompleted] AS TimeCompleted;

You stated that you are attempting to create an APPEND query. This is a SELECT query. What does your APPEND query look like? This query won't append anything. Then once you create your APPEND query, you need to run it. How are you running it. Two common ways are:
DoCmd.OpenQuery "YOUR QUERY NAME", acViewNormal, acReadOnly or DoCmd.RunSQ.

 

[LarryE]

We may have a semantics problem here. I don't think you meant to create an APPEND action query. Is that correct? You simply wish to enter data using a form? You may want to append new records using a form, but that is different than appending records using an APPEND action query.