Insert a value into a query

[swsailor]

After running a match query I foune no records returned by using the DCount function. Since am going to export the data to an excel worksheet I would like to place a message in a field to the effect that no records available. The code that I am working with, without success is:



INSERT INTO QmatQselBenefitsToTblPermBenefits ([LastName]) VALUES (No additions to report this month)

SQL language is not my forte.

Thanks for any suggestions

[ranman256]

Dont use SQL, use a query.
Queries dont need the Dcount, the query does the counting.

[June7]

Regardless of how you determine the record count, need to place literal text between text delimiters. If you are running this SQL action within VBA:

CurrentDb.Execute "INSERT INTO QmatQselBenefitsToTblPermBenefits ([LastName]) VALUES ('No additions to report this month')"

But don't you need a date value inserted as well?

[Gicu]

I don't think you need or want to actually add a record to the underlying table. For exporting purposes you can simply use a union query to show your "No records added this month".

Select QmatQselBenefitsToTblPermBenefits.[LastName]. From QmatQselBenefitsToTblPermBenefits
Union
Select "_No additions to report this month" As [LastName] From QmatQselBenefitsToTblPermBenefits;

This would create that entry and place it at the top of your last names if sorted ascending.

Cheers,
Vlad

[swsailor]

I have copied the code from GIGU and June7

separately,

and had no success.
Is it possible to place a column in the match query like the following:
Constant:IIf IsNull(LastName), LastName = "Nothing to report this month"
where LastName is a field in the empty query?

[Gicu]

Can you provide a sample db with your queries - no need for much data, just a couple of records to show us what you want.

Cheers.
Vlad

[June7]

Yes, that could be a calculated field in query. Provide query SQL or db as suggested by Gicu. Follow instructions at bottom of my post.

[swsailor]

The query returns nothing including the message. The code I am attempting is

Dim varCheckCount As Integer
varCheckCount = DCount("[LastName]", "QmatQselBenefitsToTblPermBenefits")
If varCount = 0 Then
MsgBox "No additions to report this month"
SELECT QmatQselBenefitsToTblPermBenefits.[LastName] FROM QmatQselBenefitsToTblPermBenefits
Union
SELECT "No additions to report this month" As [LastName] FROM QmatQselBenefitsToTblPermBenefits;
Else
MsgBox "These volunteers will be added to the Abenity eligiblity list"
End If

It is likely to be a syntax error or punctuation.

[orange]

You should be checking varCheckCount not If varCount = 0

You should have Option Explicit at the top of your module Always!!!

[Gicu]

Sorry but your code doesn't make much sense. Where do you use the select statement? If you want to tell users on a report that there are no new volunteers I would use the NoData event to show the message ("No additions to report this month") then set Cancel=True to cancel the opening of the report. In my example I was just trying to show you the syntax for the Union; if the first query has no records the resultant union with itself will also have no records. You need to reference the original table containing the volunteers names:

SELECT QmatQselBenefitsToTblPermBenefits.[LastName] FROM QmatQselBenefitsToTblPermBenefits
Union
SELECT "No additions to report this month" As [LastName] FROM tblYOUR_VolunterTable;

Cheers,
Vlad

[June7]

Can't just plop an SQL statement into the middle of VBA code like that. That code won't even compile. Was that just a copy/paste error in the post and code is not really like that?

And as Orange pointed out, you are not correctly referencing declared variable.

[swsailor]

After a day and a half I still have not work this out. My latest code effort is:


Dim db As DAO.Database
Set db = CurrentDb
Dim strSQL As String
strSQL = "INSERT INTO QmatQselBenefitsToTblPermBenefits (LastName)" & "VALUES ('No Additions');"
db.Execute strSQL

No errors or messages of any kind but it does not INSERT.

Is it possible that you can't INSERT INTO any fields in a blank query?
I have even tried to create an update query and that has failed.

Thanks for your patience

[Gicu]

You can't insert into a query. Can you explain what is that you want to do?

[June7]

You can INSERT into a query but it must be an editable query. And what is really happening is a new record is created in table, just passing through query.

[swsailor]

I am matching a query to a history table, which wirks beautifully. The records that don t match will be exported to an excel spreadsheet. On the occasion that the query returns no records I have to export the query anyway. What I am trying to do is put a message in the blank query stating that no records were found. (No new additions for this month). The spreadsheet then is emailed to the corporate office.

I tried to use an update sql and that didnt work either.