Modules & VBA :: If And NZ Statement Not Working In Access?
Jul 8, 2015
why this IF and Nz statement in my code below is not working. I know the me.txtrefNo value is "" (null) on a form field with property set to General Number. Therefore the Nz statement should return a "0" as I specified making the statement true as in 0 = 0 and then execute the actions below to generate a reference number however this it now happening as it's rendering the if statement as false and showing me the message " Whats going On?" which is after the else statement.
Code:
If Nz(Me.txtrefNo, 0) = 0 Then
Me.txtrefNo = DMax("[refNo]", "[R_P_Data_P]") + 1
tmpRefNo = Me.txtrefNo
'testing variable value
MsgBox (tmpRefNo)
Else
MsgBox ("What's going on?")
End If
PS. Does this have anything to do with the table field format that the me.txtrefNo value will be saved to?
View Replies
ADVERTISEMENT
Dec 27, 2014
I have a program that runs under access 2007 that I use at my work. We will soon be updating to MS office 2010 and the program will not work now because a calender file .ocx was removed from access 2010. Is there a way to get the 2007 .ocx file to work in access 2010?The program I am using is a relatively simple stand-alone and unsupported app that we use to request patient arrival and departure from various radiology tests inside a hospital. No reports are made from the app other than the number of patient transports for the day.
The app is placed on a common drive accessed from any pc in the hospital. No special permissions are required. But our app does use the calendar, time and date functions in access 2007. When I tried the app on a pc with access 2010, it basically says it (access) cannot open the app because a .ocx file is not present.Is there a way to make the access 2010 calendar file work in access 2007?
View 1 Replies
View Related
Jun 8, 2013
I used below vba code to to send email with PDF attachment in Access 2007 which works perfectly.
DoCmd.SendObject acSendReport, "R_TradeOrdAck", acFormatPDF, strMailList, , , .............
But since the db is installed at single pc user to work with Access 2003, the command acFormatPDF wont work. Instead acFormatSNP is working.
But I need acFormatPDF to work in Access 2003.
View 8 Replies
View Related
Oct 4, 2013
What I'm trying to do here is, update my access table ("Table1") from excel sheet ("Myexcel.xls"). Excel file contains all the information however access table is not updated except "RefNo" field. Condition what I coded here is when once connection is build, "RefNo" in excel matched with "RefNo" of access table , update the other fields in Access table. Which is working fine (doesn't showing any error) but still data is not updated in access table. I don't know why is not updating it....
Private Sub Command0_Click()
Dim accessCMD As ADODB.Command
Dim accessRS As ADODB.Recordset
Dim accessParam As ADODB.Parameter
Dim bFound As Boolean
[code]....
View 3 Replies
View Related
Aug 3, 2006
Expr1: (IIf([FixedExpenseOccurance]="Quarterly",[amount]/3,0))+(IIf([FixedExpenseOccurance]="SemiAnnually",[amount]/6,0))+(IIf([FixedExpenseOccurance]="Yearly",[amount]/12,0))
FixedExpenseOccurance is the the field and "*" is the values which get looked up from a table. I am trying to create an expense report. The query runs but no values are entered into the box?
For some reason it is asking for a parameter when I try t orun it. I don't have anything entered into the Criteria.
View 10 Replies
View Related
Nov 23, 2007
hi i am trying to use the following iif statement in a query.
IIf([Forms]![MainForm]![cmb_ExcessStatus]="Resolved",1,IIf([Forms]![MainForm]![cmb_ExcessStatus]="Outstanding",2,IIf([Forms]![MainForm]![cmb_ExcessStatus]="All Records",([MainTable].[Status])=1 Or ([MainTable].[Status])=2,([MainTable].[Status])=1 Or ([MainTable].[Status])=2)))
So, if user selects 'Resolved' then use 1
if the user selects 'Outstanding' then use 2
if the user selects 'All records' then i want both 1 and 2 but this will not work. i also want the default to be 'All records'
Any help would be much appreciated.
View 1 Replies
View Related
Aug 3, 2006
Expr1: (IIf([FixedExpenseOccurance]="Quarterly",[amount]/3,0))+(IIf([FixedExpenseOccurance]="SemiAnnually",[amount]/6,0))+(IIf([FixedExpenseOccurance]="Yearly",[amount]/12,0))
I have this in a query and the query runs, it just doesnt do anything. FixedExpenseOccurance is the field name, "*" is the field values (they get looked up from a table). Any clues why this is not working?
For some reason it is asking for a parameter when I try t orun it. I don't have anything entered into the Criteria.
View 8 Replies
View Related
Aug 10, 2006
(IIf((NextActivtion)>=(Today)),((DueToday)="yes"),((DueToday)="No"))
Says I have the incorrect number of Arguments? Any clues? These are all field name:
NextActivation (Is a date)
Today (is today's date)
DueToday (is a yes/no)
View 13 Replies
View Related
Mar 2, 2008
i have an sql statement:
aa = "hello"
Tasklist.RowSource = "SELECT [Tasks].Staff_Name, [Tasks].Project_Title, [Tasks].Percentage_Complete FROM [Tasks] WHERE [Tasks].Date_Allocated < aa ORDER BY [Tasks].Staff_Name;"
how can i make it look the the data i put in "aa" rather than prompt a msg box asking for "aa"?
View 5 Replies
View Related
Oct 9, 2005
hi im trying to do the following if statement in a query but its not working, anyone know what im doing wrong?
any help would be great.
-------------------------------------------------------------------------
New: IFF [(TABLE-SALES]![PERIOD]<=[TABLE-AVERAGE]![ACTUALS_TO_PD] then [TABLE-SALES]![ACTUALS_TO_PD]) else
[TABLE-UPDATE]![FORECAST_1_BASE_VALUE]
------------------------------------------------------------------------
im trying to say if the field "period" in the table: "TABLE SALES" is the same or less than the value in the field "actuals to pd" in the table :"TABLE-AVERAGE" then the value displayed in this field ("new") should be the value: "actuals to pd" in the table "table-sales" otherwise the value in the field "forecast1 base value" in the table "table update"
View 4 Replies
View Related
Apr 5, 2006
Hi,
I am using an ADO connection to an access Db but am having from executing a query on the table.
In my table I have a Field named [Date] with Data Type as Date/Time (Short Date dd/mm/yyyy). When I perform a Query on the Db like;
SELECT [Date], [Part No], [Batch Qty] FROM [Speed Fastener Packing] WHERE [Date] LIKE '28/03/2006'
The above works fine and produces results, but if I do the following - then it come up with error - data tyle mismatch;
SELECT [Date], [Part No], [Batch Qty] FROM [Speed Fastener Packing] WHERE [Date] = '28/03/2006'
Any Ideas Why? TIA
View 1 Replies
View Related
Apr 5, 2006
Hi,
I am using an ADO connection to an access Db but am having from executing a query on the table.
In my table I have a Field named [Date] with Data Type as Date/Time (Short Date dd/mm/yyyy). When I perform a Query on the Db like;
SELECT [Date], [Part No], [Batch Qty] FROM [Speed Fastener Packing] WHERE [Date] LIKE '28/03/2006'
The above works fine and produces results, but if I do the following - then it come up with error - data type mismatch;
SELECT [Date], [Part No], [Batch Qty] FROM [Speed Fastener Packing] WHERE [Date] = '28/03/2006'
Any Ideas Why? TIA
View 3 Replies
View Related
Dec 5, 2006
hi guys i have a form that comes from a query with a parent and child table. Main table pk is pricingid and then the foreign key in tblHistory is pricingid.
I am trying to do an append table but i keep getting an error message
Pricing ID is the pk autonumber in tblPricing (parent table)
historyid is the pk in tblHistory and pricingid the fk(child table)
oldprice should be a currency value
the other fields are text.
this is my code:
strSQL = "Insert into tblHistory (DateChanged, Edit_UserName, OldPrice) Values(#" & now() & "#, '" & getusername() &"', " & Me.txtPrice.OldValue & ")
but i get the error message
You cannot or change a record because a related record is required in table tblPricing
any advise... i know i need to select somehow the pricingId from the parenttable, but how do i do that?
View 8 Replies
View Related
Aug 22, 2014
I am attempting to filter records using a multi-select listbox, but all records are being returned. Here is my code.
Private Sub btnKeyboxCount_Click()
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String
[Code] ....
View 1 Replies
View Related
Apr 19, 2014
I'm using MS Access 2003 and having difficulty with VBA.
Code:
strSql = "Insert into TBLCONDUTOR (Codigo, pretensao, ncarta, nlicenca, nome, apelido, dnascimento, contacto1, contacto2, email, nbicc, nnif, morada, localidade, cidade, cpostal, _
imagemcondutor, catAM, dataemissaoAM, datavalidadeAM, catA1, dataemissaoA1, datavalidadeA1, catA2, dataemissaoA2, datavalidadeA2, catA, dataemissaoA, datavalidadeA, _
catB1, dataemissaoB1, datavalidadeB1, catB, dataemissaoB, datavalidadeB, catBE, dataemissaoBE, datavalidadeBE,
[Code] ....
View 3 Replies
View Related
Jun 21, 2013
I am trying to created an IIF statement in a field I have in a form.
I have entered the following statement but it keeps coming up with the following error and I cannot see where I am going wrong.
The expression you entered contains invalid syntax, or you need to enclose your text data in quotes
=IIf([RenewalInstallment]=0,(12-[MFAccepted])*[CurrentAnnualPremium]/12)*(1-[DiscountAccepted]/100),IIf RenewalInstallment]<>0,(12-[MFAccepted])*[RenewalAnnualPremium]/12)*(1-[DiscountAccepted]/100)
If I enter the first part of the If statement i.e.
=IIf([RenewalInstallment]=0,(12-[MFAccepted])*[CurrentAnnualPremium]/12)*(1-[DiscountAccepted]/100)
The field calculates correctly however there seems to be something wrong with the nested if.
View 6 Replies
View Related
Apr 8, 2015
Access 2013
I've created a table for a college project and realised that one of my calculated fields isn't working. It doesn't produce an error message, it just doesn't calculate.
IIf([CycleSpeed]<10,[CycleTime]*236,IIf(10<=[CycleSpeed]<12,[CycleTime]*354,IIf(12<=[CycleSpeed]<14,[CycleTime]*472,IIf(14<=[CycleSpeed]<16,[CycleTime]*590,IIf(16<=[CycleSpeed]<=20,[CycleTime]*708,IIf([CycleSpeed]>20,[CycleTime]*944,0))))))
View 6 Replies
View Related
Sep 14, 2005
Hi,
I've created a database in access 2003 with the default file format being Access 2000. Everything works fine on my computer and many others, all of whom are running different versions of access (2000, 2002 & 2003).
I have not had any problems with this database until recently and in the last few weeks we have had 2 users, both of them being Access 2000 users who have had the same problem.
The problem they are having is that when they click on any of the switchboard items, the buttons depress an then come back up again when the cursor is moved away, but nothing else happens - no action, no error message - nothing!!! Its the same for all of the buttons on the switchboard.
Does anyone know what is causing this? I have another computer with Access 2000 on it and this works fine, as it does on several other computers runiing Access 2000, I just cannot understand why it is not working on these 2 particular machine.......
Many thanks in anticpation.
Steve
View 3 Replies
View Related
Dec 7, 2005
i am trying to use access 2000 database onto to access 2003. When i try to click design mode to change something it gives me this message:
You dont have permissions to modify.
If i run it on access 2000 it runs fine.
Someone please help.
View 5 Replies
View Related
Feb 18, 2014
Code:
---------
If (Me.Variance_From_Budget < 99999 And Me.Variance_From_Budget >= -99999)
---------
This code is working when variance is less then -100,000, but does not work when variance is greater then -100,000.
View 14 Replies
View Related
Nov 2, 2014
Every time i try to get info from a sub form or its table ,Things never sem to work.Is there something special you have to do for eg maybe going via the main form?I am trying to use dsum as an alternative to multiplying info in the sub form.This is the code
=DSum("SoldAtPrice*Quantity","[Order Details]","IDNewOrders=" & [OrderID FK]).
View 4 Replies
View Related
Oct 21, 2013
I have an afterupdate event that is supposed to send an email when a box is updated. Most of the times the code works fine except for one case.
The code checks the value of a field in subform in another tab of the same form
Code:
'find the MSM date
[Forms]![Project Main]![Marketing Specialist Main]![MSMDate].SetFocus
MSMDate = Nz([Forms]![Project Main]![Marketing Specialist Main]![MSMDate].value, "__-___-__")
This field (MSMDate) keeps its value in a separate table.
The problem comes when there is no value in this other table for the given project. If this is the case, the subform does not even appear on the main form (it is all a grey box) and the Setfocus returns an error.
I tried to comment the setfocus line to see what the NZ function would do in the next line and it also returns an error.
It is almost as if the subform was not "initialized" for that particular record...
How can I account for those cases?
View 4 Replies
View Related
Nov 8, 2014
I have a select clause, it executes fine, BUT the totamt is not calculated. The field is Amount. The code is not working.
strSQL = "SELECT Account, Dept, " & "Sum([Amount])" & " As [totamt] FROM TB201410 GROUP BY Account, Dept;"
View 5 Replies
View Related
Nov 1, 2014
I have a combobox, and the 'OnNotInList' event has code to show a message box, then set focus to another control. That all works fine, however I am still getting the system generated 'Not in List Warning', despite the code for the not in list event starting with DoCmd.Setwarnings (False)
How to disable the warning?
View 2 Replies
View Related
Jan 26, 2015
I am trying to carryout working days between two dates (Excluding weekends Friday & Saturday) but unable to fix it correctly, However i did search a lot over internet also about built in function but all is showing i have to add it module or macro etc.So, i select the module for Access 2010 but also not finish with function.Table name is "LeaveSettlement" and column name is "Total_Wdays", what field type i have to select for result column.
Public Function LeaveSettlement(ByRef Leave_Start As Date, _
ByRef Leave_End As Date _
) As Integer
' Returns the number of weekdays in the period from Leave_Start
' to Leave_End inclusive. Returns -1 if an error occurs.
' If your weekend days do not include Saturday and Friday and
' do not total two per week in number, this function will
' require modification.
[code]...
View 8 Replies
View Related
Oct 30, 2013
I have a form I use to search for clients when I hit a command button, using surname etc. I'd like a yes no button to pop up rather than the clunky ok button route I currently have. I'm a beginner with VBA and most this I've gleaned from internet.
If there are matching results, everything is fine. If there are no matching results, I've managed to get my desired messagebox come up by using the code below in the onload event of the resulting search results form, however the yes no buttons do nothing and the results form loads blank as though no records were found.
I'd like Yes to send users to the client input form, and No to return them to the search form.
Private Sub Form_Open(Cancel As Integer)
If Me.Recordset.RecordCount = 0 Then
MsgBox "Do you want to add a new Client?", vbQuestion + vbYesNo, "No clients found"
Select Case intanswer
Case vbYes
[Code] .....
View 9 Replies
View Related
