What Is Wrong With This Sql Statement In Vba

Sep 30, 2005

CurrentDb.Execute "INSERT INTO tblArchivepayment
(EntryDate,MemberID,PCNumber,ApplicationType,Timei n,TimeOut,UsedTime,
Amount,PaymentType) SELECT (EntryDate,MemberID,PCNumber,ApplicationType, TimeIn ,
TimeOut, UsedTime,Amount,PaymentType) FROM tblPayment WHERE MemberID = '" & MemberID & "'"


i got syntx in comma error

View Replies


ADVERTISEMENT

What Is Wrong With This If..then Statement??

Oct 12, 2004

In the following if...then statement, even though the Debug.Print prints a "Null"
the Else part of the statement is executed. Any help is appreciated

Debug.Print Forms!frmReport!txtCustom1
'if id combo box is null then
If Len(Forms!frmReport!txtCustom1) = Null Then
Debug.Print "Then"
'Filter Null strCompanyName records
rst.Filter = "strCompanyName <> """""
Else
Debug.Print "Else"
' filter on portfilio id
rst.Filter = "strJobID = """ & Forms!frmReport!txtCustom1 & """"
End If

Output:
Null
Else

View 1 Replies View Related

What's Wrong With This Statement

Feb 3, 2006

I can't seem to get this to work. This issue was addressed in a previous thread, but it's not working for me. Help

Private Sub append_data_Click()
Dim strSQL As String
DoCmd.SetWarnings False
strSQL = "INSERT INTO sob_org_item_Mon11_Step1 ( ORG, Item, [Set of Books] )" & _
"SELECT [Mon10 Perpetual 01400 Qty_Amt].ORG, [Mon10 Perpetual 01400 Qty_Amt].Item, SOB_FX_Rates.[Set of Books]" & _
"FROM SOB_FX_Rates RIGHT JOIN [Mon10 Perpetual 01400 Qty_Amt] ON SOB_FX_Rates.Org=[Mon10 Perpetual 01400 Qty_Amt].ORG" & _
"GROUP BY [Mon10 Perpetual 01400 Qty_Amt].ORG, [Mon10 Perpetual 01400 Qty_Amt].Item, SOB_FX_Rates.[Set of Books];"
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

View 1 Replies View Related

IIF Statement Calculation Is Wrong

May 18, 2007

I've tried this IIF statement in both a report and a query with the same result. I have another one that is almost identical that is correct. Here is the IIF in my query:Section 3 Total T&E: IIf([Section 3 Subotal]>"6000","$6000.00",[Section 3 Subotal])Here are the results:Section 3 Subotal -- Section 3 Total T&E$4,727.20 -- 4727.2$12,064.13 -- 12064.13As you can see, the results are not correct since 12,064.13 is greater than 6000.Any idea what I'm doing wrong?Thanks for your help!Mare

View 6 Replies View Related

Whats Wrong With This Sql Statement?

Sep 1, 2005

SELECT Fields
FROM table1 RIGHT JOIN table2 ON table1.Pubid = table2.PubID
WHERE (table1.PubType=[Forms]![frmCirculationByTitle]![comMemberType] Or [Forms]![frmCirculationByTitle]![comMemberType] Is null)
AND (table2.lAuditPeriod=[Forms]![frmCirculationByTitle]![comStartPeriod] Or [Forms]![frmCirculationByTitle]![comStartPeriod] IS null)
And (table2.lAuditPeriod=[Forms]![frmCirculationByTitle]![comEndPeriod] Or [Forms]![frmCirculationByTitle]![comEndPeriod] is null)
AND (table2.Unfinancial=[Forms]![frmCirculationByTitle]![chkCurrent] Or [Forms]![frmCirculationByTitle]![chkCurrent] IS null);

even if i only have one parameter (a member type, which should bring back at least 20 for each type of member) it returns nothing. if i do the same sql statement in a query and hard codee the value in i get data. it seems to be ignoring whatever is on the form.

*shrug*

%simon

View 2 Replies View Related

What's Wrong With This Create Statement? (Urgent)

Jan 10, 2006

CREATE TABLE Jobs
(jobname LONGTEXT(1000) NOT NULL, [desc] LONGTEXT(1000) NOT NULL,
[req] LONGTEXT(1000) NOT NULL, [specialreq] LONGTEXT(1000) NOT NULL)

Thanks!

View 1 Replies View Related

Help Needed Urgently - Whats Wrong In This SQL Statement

Mar 11, 2006

SELECT (Max(ReferenceDescription.ReferenceID))
FROM (ReferenceDescription)
WHERE (ReferenceDescription.ReferenceID >= (ReferenceDescription.CategoryID * ReferenceDescription.DepartmentID) AND ReferenceDescription.ReferenceID <= (INT(ReferenceDescription.CategoryID * ReferenceDescription.DepartmentID * 0.9999))

View 1 Replies View Related

Queries :: Update Statement For A OUTER JOIN Select Statement

Feb 12, 2014

I have the following Select Statement:

SELECTTenant.ID, Tenant.[First Name], Tenant.[Last Name], Tenant.Address, Tenant.City, Tenant.State, Tenant.Zip, Tenant.[Home Phone], Tenant.[Cell Phone], Tenant.[Work Phone], Tenant.[Rented Unit],
Tenant.[Security Deposit], Tenant.[Move In], Tenant.[Move Out], Tenant.TenantID, Tenant.UnitID, Tenant.PropertyID, Tenant.OwnerID, Owner.Company, Owner.ID AS Expr1, Property.[Property Address],

[code]....

Now, I know that something in the UPDATE statement does not match my select statement.What should my Update Statement be, in order to update all the columns in the joined tables?

View 2 Replies View Related

Queries :: IIF Statement With And In True Statement

Oct 31, 2014

I have a query with the following criteria in one of the fields:

>=DateAdd("m",-12,fom()) And <=DateAdd("m",1,fom())

fom is a function for first of the current month. I need this query to be specific to what month it is when its ran so i want to only have this criteria if the month is > = october. If it isnt October or greater, i want the criteria to reflect this.

>=DateAdd("m",-12,fom()) And <=fom()

Which also works by itself. But when i add it to an iif statement it always produces no results. Below is the iif statement.

Iif(month(date())>=10, >=DateAdd("m",-12,fom()) And <=DateAdd("m",1,fom()),>=DateAdd("m",-12,fom()) And <=fom())

I have also added the column name to each expression and it still doesnt produce any results.

View 4 Replies View Related

Creating IIF Statement In Between Statement For Dates?

Apr 28, 2015

I am creating a multi-search form for a student database, where after I enter my search criteria I hit a "Run Query" command button and then it opens a query form with all of my criteria.So far I can search using last name, first name, and middle name. When I try to search with a start date and end date I am have issues.The start date and end date is for the class date. In the query form under the field, class date, for criteria I wrote:

Between IIf([Forms]![Search Form]![Start Date]="",1/1/10,[Forms]![Search Form]![Start Date]) And IIf([Forms]![Search Form]![End Date]="",4/25/15,[Forms]![Search Form]![End Date])

I want it when I write a date in the start date and end date I want it to give me a list of all the students who took the course between those dates. Also, if I leave the dates blank I want it to search all dates. The dates 1/1/10 and 4/25/15 are just the dates I gave because that is far back as my database goes.

View 11 Replies View Related

Now Its All Gone Wrong!!!

Sep 21, 2005

Ok Guys,

Here goes...

all of a sudden now, when i open my form, its not displaying any of the previous data that i stored in it!! All the data is still in the table, and when i add data it goes into the table!! I checked properties and the record source and everything is ok there, what have i done wrong guys...HELP!!!!

View 3 Replies View Related

What's Wrong?

Jun 9, 2006

I purchased a copy of Office 2003, on Ebay, for less than half the price it normally sells for. I created databases, which work well on my computer. However, when I copy them over to the office computer from my computer, via Flash memory stick, the program does not run. The message seems to indicate a problem with registration. When I installed Access, it asked me for a registration key, which I did provide. After that it worked-not activation process. Does this mean that you can only use the computer you created the database on to run it, or that I do not have a proper version of Access 2003? I cannot transfer, from my home computer, to the office computer. Is this Microsoft paranoia again?
Subsequent uninstalls and reinstalls of the program did not ask for the installation key.
What should happen? I would like to resolve this major problem and your help will be greatly appreciated. The program is flaky. Also, Switchboard does not work-must use Option Box.

These questions most user must know about initial installation and copying mdb files to other computers -please reply.

View 2 Replies View Related

What's Wrong

Oct 16, 2006

Hi,
Can anyone tell me what's wrong with this:
DoCmd.RunSQL "INSERT INTO tblPersoaneJuridice (Denumire,Localitate,CodUnicInregistrare,NumarInre gistrareRegComertului,Telefon,Fax) VALUES ('" & Forms!frmPrincipal!subfrmTipVersus!txtDenumire & "','" & Forms!frmPrincipal!subfrmTipVersus!txtLocalitate & "','" & Forms!frmPrincipal!subfrmTipVersus!txtCUI & "','" & Forms!frmPrincipal!subfrmTipVersus!txtNumarInregis trare & "','" & Forms!frmPrincipal!subfrmTipVersus!txtTelefon & "','" & Forms!frmPrincipal!subfrmTipVersus!txtFax & "')"
cui = DLookup("[CodUnicInregistrare]", "[tblPersoaneJuridice]", "[NumarInregistrareRegComertului]='" & Me!subfrmTipVersus!txtNumarInregistrare & "'")

i have a string variable denoted by cui and i need it to take value from table tblPersoaneJuridice from field CodUnicInregistrare. CodUnicInregistrare and NumarInregistrareRegComertului is text.

View 3 Replies View Related

Would Someone Tell Me What's Wrong With This

Jun 14, 2007

Have not coded for about 5 years and need a little refresher. Can't remember what I need to do. Thanks

Dim SelectedCompany As String
Dim rs As ADODB.Recordset
Dim Cn As ADODB.Connection
Dim sSql As String

Set rs = New ADODB.Recordset
Set Cn = New ADODB.Connection

SelectedCompany = Me!cboCompanyName.Text

sSql = "select Customername, AddressLine1, AddressLine2, AddressLine3, AddressLine4, AddressLine5, AddressLine6 from customers where "
sSql = sSql & "CompanyName = '" & SelectedCompany & "'"

rs.Open sSql, Cn, adOpenStatic, adLockReadOnly

this last line fails with error "No value given for one or more required parameters."

The code is executed when someone selects something in a combo box on a form in an access database.

Thanks

Pete

View 4 Replies View Related

Am I Doing This Wrong

Oct 4, 2007

I think I'm just tired, and not seeing what to do here.

I have my main table.

MainID PK
bunch of other fields...

I also have a 'created by table'.

Createdby_ID PK
User ID
Main ID
Date_created.

I also have a user table, with a list of all the users.
User_ID PK
User_Name.


Is this the right way to setup these tables?
Only 1 person can create any paticular record.

I want it to be required tha whoever creates the record selects their name and dates it.

View 11 Replies View Related

What Is Wrong Please Help

Dec 13, 2005

strSQL = ("SELECT onespace2(MeetingMinutes!MinutesID, '.' ) & " - " & [MinutesTitle] AS Item, MeetingMinutes.MeetingCode, Minutes.*, MeetingMinutes.MinutesCode, MeetingMinutes.MinutesID ")
strSQL = strSQL & ("FROM Minutes INNER JOIN MeetingMinutes ON Minutes.MinutesCode = MeetingMinutes.MinutesCode ")
strSQL = strSQL & "WHERE MeetingMinutes.MinutesCode= " & MinutesCode & " AND MeetingMinutes.MeetingCode= " & [Forms]![MinutesMFnew]![ComboMeetingNav] & ";"

cant get it running.
i got the same query running made in the querybuilder, but somethings change, so i need to adjus it in vba. But it doenswork, and get a type error

please advice

View 1 Replies View Related

What Is Wrong With This SQL?

Jul 13, 2007

DoCmd.RunSQL "INSERT INTO tblAccessLog " _
& "(DateLoggedOut, TimeLoggedOut) VALUES " _
& "(#" & Date & "#,#" & Time & "#) WHERE " _
& "(LogID = '" & LogsID & "');"

Could anyone possibly tell me why this isn't working? Probably something stupid i've done bus still.

When i run it, I am getting the error message "Query input must contain at least one table or query" yet i've told it to use tblAccessLog so i'm just confused.

Thanks

View 8 Replies View Related

What Is Wrong With This SQL?

Aug 14, 2007

StrSQL = "INSERT INTO LV (Directory_Name:,Filename:, Total_Files,Total_Records:,Total_Batches:, Total_YH) VALUES ('" & DirectoryName & "', '" & FileName & "', TotalFiles , TotalRecords , TotalBatches , TotalYH);"

Have tried loads of combinations of the " ' and ' " to get it working but just keeps giving me the damn syntax error!!! :mad:

What's wrong with it? Thanks

View 2 Replies View Related

Anything Wrong With This Sql

Oct 11, 2007

strSQL = "INSERT INTO TBL_History (Filename, Agent, SentRecieved, Accepted, Notes) VALUES ('" & [Forms]!FRM_ImportSubmission.submission_filename & "','" & [Forms]![FRM_ImportSubmission]![cboAgent] & "','Recieved','True','');"

CurrentProject.Connection.Execute strSQL

i've posted it as the sql string is built in the code

i get a 'catastrpohic error' when running it from vb like this and when i build put the string into a query and run it through the access user interface it worrks

View 14 Replies View Related

Where Have I Gone Wrong!!

Oct 23, 2007

I made a number of changes to a DB that was working fine (normalised further). So what's wrong? I have a number of forms (for my sample DB I have stripped down to just one, if I can resolve for this I can apply to others).

qryENTRY_COMPUTERS
ENTRY COMPUTER FORM

The user adds a new computer then selects the Computer Make (qryLOOKUP_COMPUTER_SUPPLIER) & Computer Model (qryLOOKUP_COMPUTER_MODELS).

This now returns error 'recordset is not updateable'.

The query on which the form is based is incorrect (qryENTRY_COMPUTERS). Previously I was able to add tblSupplier & tblSupplierProducts to the query then add field ProductDescription fom tblSupplierProducts to the query. This allows users to select a value from cboComputerModelID (ProductID), field ProductDescription then displays the related descriptive value for the ProductID selected (value remain on screen when user naviagtes between records.

I have attached a sample database (access 2002-2003 format).

I am sure the answer is staring me in the face but I have now spent hours trying to resolve!!

Help very much appreciated.
Phil.

Update: One difference I can see is that on the query 'qryENTRY_COMPUTERS' where i have added tblSupplierProducts & tblSupplier the join properties are in reverse i.e. Left Table Name = tblSupplierProducts Rigth Table Name = tblComputers Left Column Name = ProductID Right Column Name = ProductID ????

I can't edit this.......................

View 14 Replies View Related

Could Someone Tell Me What I'm Doing Wrong

May 6, 2005

i cant seem to get this to work.

any help would be greatly Appreciated

=sum(iif([SADate]>=DateSerial(Year(Date()),Month(Date()),[p]))

thanks in advance

View 3 Replies View Related

What Is It I Am Doing Wrong??

Jul 20, 2005

I am trying to create a form that has an unbound text box which row source is a field in a table. I have created a subform that gets the records from another table.

Both tables are linked in a one to many relationship. When a user selects a record in the cmbo box i want the table to show only those records which are related.

This obviously didnt happen. So i changed the subform instead of a table as its source but a query, then in the criteria of a query just set it to look at the cmbo on the form. After the cmbo was updated i got the subform to requery.

This worked fine, but i was unable to make any changes or updates but with no particular error.


When inserting a subform using the wizard i do remember coming across a section of a wizard that asks me if i would like to tie in the subform with something on the form. However this part of the wizard just doesnt show. Maybe i was using a different version of access for this wizard?? i am currently using Access XP.

Am i really going MAD!!?

View 3 Replies View Related

Please Tell Me What Is Wrong

Aug 30, 2005

I'm trying to mess with a combo box where when one user selects a value a form opens up. This is the code I'm typing in but I keep getting a syntax error

DoCmd.OpenForm(Phone,[View As AcFormView=acNormal],[DataMode As AcFormOpenDataMode=acFormAdd],[WindowMode As AcWindowMode = acWindowNormal],[OpenArgs])

View 5 Replies View Related

What Am I Doing Wrong?

Aug 15, 2006

Hi,
Trying to make the selection in a combo box on frm1 be the filter on frm2.

I found this example by searching the form, but it's not working for me

Option Compare Database

Private Sub Command3_Click()
On Error GoTo Err_Command3_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "form_lastnametotal_exludeholidayform"

stLinkCriteria = "[Employee_Name]=" & Me!cboMyCombo
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command3_Click:
Exit Sub

Err_Command3_Click:
MsgBox Err.Description
Resume Exit_Command3_Click

End Sub

I get an 'the openform action was canceled' error?

Any ideas?
The second form is based on a query

Thanks

View 10 Replies View Related

Where Oh Where Am I Going Wrong??

Sep 26, 2006

Hello once again. I have been working on a small database that records all IT records (Desk Info / Software / License Info / Hardware etc...) Somebody out there might even find it useful!! Will post to DB forum when working :)

Where to begin. I thought it would be a good idea to normalize a little more........(still not 100% but an improvement). I seperated Software/License information. I now have tblSoftware & a tblLicenseDetails.

Basically the clux of it all centers around a form 'frmDeskSetup'. You can search via three methods (Desk ID / User ID or Computer ID). I would like to amend the Installed Software Form (last form on frmDeskSetup) to include the License Information.

I have also tried to create a query linking tblComputerSoftware tblSoftware + tblLicenseDetails. My idea was to create a form that would allow users to Add Software/ Add a License for that Software and then allocate the licensed software to a computer all from one form. The query run OK but would not allow data entry via the form!!

Sorry for the length of this post. I have attached a stripped DB for forum in Acess 2003 format.
Thanks in advance,
Phil.

View 5 Replies View Related

Can Anyone Tell Me What Is Wrong With This?

Sep 3, 2004

IIf([Count: ERName]>=5,"Yes","No")

What is wrong with this line? I am entering it in the following query:

ERName Group By
Department Group By
Date of Call-In Group By
Type of Call-In Group By
Count:ERName Count

The other thing I tried is just doing another query based on the above query and doing a totals (sum) line on Count:ERName. That doesn't work, it prompts me for count (as if that is a parameter I want to choose.

Any ideas? Sorry to be a pain. Thanks!

KellyJo

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved