Modules & VBA :: SQL And VBA Insert Null For Unfound Date
Jun 17, 2013
I'm trying to search through a table with serial card IDs, Order Numbers, and Ship Dates. I'm search through the Serial Card ID column and making my code work so that if the serial card ID exists and its ship date is between 9/30/2001 and 10/1/2011, then retrieve the associated order number and put in array. All of this works perfectly except for one tiny part : it's not returning a null for either unfound serial Card ID or serial card Ids that exist but their ship dates do not qualify. I would like to return a null value for these fields into the array as well as keep the found values in the array. The whole point of all of this is so I could count the number of rows for all the found order numbers and return a total value with that number.
View Replies
ADVERTISEMENT
Oct 2, 2014
If a new record is created, insert date only if customerID is not null
Private Sub Form_Current()
If "CustomerID", "TblDietPlan" = <> 0 Then
If Me.NewRecord Then Me.MealDate = DMax("MealDate", "TblDietPlan")
End Sub
View 1 Replies
View Related
Sep 22, 2006
Hello guys,
Does anybode has any idea, of how to do that ? I can do it very easy in VB.NET, or C#, but in Access I give up.
So, I have to take data from the form , and send it to Sub.
With data type of String , I have no any problem, use Nz funciton, end everything, goes well.
But with Date and Integer, I can not find solution.
So :
Date
Dim PensionerFromDate as Date
PensionerFromDate = IIf(IsNull(Me.txtPDPensionerFromDate) = True, ????, Me.txtPDPensionerFromDate)
(instead of ???? i tried everything .. dbNull, vbEmpty, vbNull,sqldatenull, and somtimes it works but int the table stores "12/301899")
Integer
Dim CompanyID as integer
CompanyID = IIf(Me.cboCompany.Column(0) = 0, ????? , Me.cboCompany.Column(0))
I tried here instead of ????, tu insert "", ",," , " " , Cint("") itd. itd. . but nothing works.
Has anybody any idea ?
100 x thanks in advance
View 7 Replies
View Related
Mar 12, 2014
I have a form with Date of Death (DOD) field. I would like update DOD from a table dbo_patient into Z_Patients table.
I have set the datatype as Date/Time in the form for Date of Death.
Code:
Private Sub Update_DOD()
Dim rcMain As New ADODB.Recordset, rcLocalDOD As New ADODB.Recordset
Dim DOD As String
rcMain.Open "select distinct PatientKey from Z_Patients", CurrentProject.Connection
[Code] ....
However I am getting some error Run-time error '-2147217913 Date type mismatch in criteria expression in section below.
Code:
CurrentProject.Connection.Execute "update Z_MAIN_Processed_Patients set DateOfDeath = '" & rcLocalDOD!date_of_death & "' where PatientKey = " & !PatientKey
View 5 Replies
View Related
Jul 17, 2015
On a form I have a textbox with a data type of Date/Time. If the user has the cursor in that text box and types the letter "t", I want to automatically insert today's date. I also want them to have the option of manually typing in a date, i.e. 05/12/2001 or use the Date Picker "calendar" item. This is using Access 2010.
View 3 Replies
View Related
May 11, 2014
I have an access database with a query which inserts system date/time from Now() function to a column. My system date is 12-05-14 (12 May 14) and when it is inserting in the table is taken as 05-12-14 (5 Dec 14).
View 2 Replies
View Related
Jan 13, 2015
I would like to enter a week dates forward after looking up the last entered date. i have working codes that looks up the last date and increments it by one and enter the date into a new field.
Here is what i have:
Private Sub Form_AfterUpdate()
Dim dtmNextDate As Date
Dim dtmLastDate As Date
Dim SDate As Date
LastDate = Nz(DMax("[SDate]", "schedule"), _
DateAdd("d", -1, Date))
NextDate = DateAdd("d", 1, LastDate)
CurrentDb.Execute "INSERT INTO Schedule(SDate) VALUES ('" & NextDate & "');"
End Sub
enter dates into 5 new fields instead of one using a loop maybe.
View 6 Replies
View Related
Sep 12, 2014
I am dabbling in updating an existing database.Currently our staff enter data via a form and I have this set up using the below script to automatically complete the "Due date" field with a date 5 working days ahead of the current date, saving them from having to manually enter this date each time.
Code:
Public Function PlusWorkdays(dteStart As Date, intNumDays As Long) As Date
PlusWorkdays = dteStart
Do While intNumDays > 0
[code].....
This then updates the value on the relevant form object using the default value "PlusWorkdays(Date(),5)" to give a date 5 working days ahead. the problem I face is that due to our business process at 15:30 each day our due date needs to change to 6 working days from todays date rather than 5. I was wondering if there is a simple way I can modify the script to add an extra working day when the local time hits a specified point (15:30 in this case), unfortunately this is a bit outside my abilities currently!
View 3 Replies
View Related
Sep 23, 2014
I'm new to programming with Access but am attempting to create a new field in a table with an new date based on existing fields in the table.
The current fields are [Frequency], integer, [Risk], text, [Last Audit Date], date/time, and the new field is [Next Audit Date]. [Frequency] is a calculated field based only on [Risk] and is equal to "5" if [Risk] is "Low" and is "3" is [Risk] is "Medium" or "High", and [Frequency] is blank if [Risk] is (thus far it has never been empty).
What I need the new calculated field to do is return "N/A" (or blank, or anything easily separated really) if [Frequency] is blank, or if [Risk] is "Low" or "Medium". If [Risk] is "High", [Next Audit Date] should be equal to [Last Audit Date] plus 3 years. When I try to save the code, I get this message: "The expression could not be saved because its result type, such as binary or NULL, is not supported by the server."
This is my code now:
IIf(IsNull([Frequency]),"",IIf([Risk]="Low","N/A",IIf([Risk]="Medium","N/A",IIf([Last Audit Date]="N/A","N/A",[Last Audit Date]+Year(3)))))
View 4 Replies
View Related
May 2, 2007
Using the sql profiler gives me no clue.
This is the insert query from the profiler.
exec sp_executesql N'INSERT INTO "ENVIS_GSD".."wat_springflow" ("result_dt","site_id","result_va","remark_tx") VALUES (@P1,@P2,@P3,@P4,@P5,@P6)', N'@P1 datetime,@P2 int,@P3 float,@P4 nvarchar(4),@P5 varchar(3),@P6 datetime', 'Feb 10 2009 12:00:00:000AM', 3000723, 8.000000000000000e-001, N'test'
The two records that I get the proplem on are not included here, but somehow they have been updated. I traced the access code, but I could see no reference to them that they were used for the update in either the form or any modules called.
Somehow up to a few weeks ago those two columns were updated.
Tracing the code and using the immediate window the user_name variable stores the login name corectly.
Someone mentioned "error 28" any ideas?
Thanks in advance
View 1 Replies
View Related
Nov 16, 2014
I do not understand what is happening here. I have foll0wing line in a calculated query field:
m: Switch([EmpID]<5,1) ' run Query 18 in attached example, A2007/2010
this produces 1 for all EmpID<5 and Null for all other EmpID's. All as expected.
But if I do this:
m: Switch([EmpID]<5,1,[EmpID]>=5,Null) ' run Query 19 in attached example
then the entire column is set to Null
View 2 Replies
View Related
Jul 5, 2013
It might be an easy one but I just wasted the past hour deciphering through my code in order to solve the run-time error '94' that I'm getting when trying to execute the following code:
Code:
Private Sub cmdUpdateDates_Click()
'###################################
'This sub aims at combining the timesheet date and the start and end time into the fields [Start Time] and [End Time].
'###################################
Dim intCounter As Integer
intCounter = 0
Dim rs As ADODB.Recordset
[Code] ....
View 1 Replies
View Related
Oct 10, 2014
i have just started to use access and i know how to insert the current date into a field using date() but i am not sure will this change the date everytime i open the form ? i want to create a form for invoices that shows the date the invoice was created and doesnt change if i re open for editing,
View 1 Replies
View Related
Aug 8, 2013
I have a query where I display the [OPEN DATE] and [CLOSE DATE] of my cases. However, when I run this query sometimes the cases are not closed yet, therefore there are null values. However, I also have a field to calculate the datediff between these two dates. I need the [CLOSE DATE] field to display today's date when it is a null value so that I can still get a count of the days using datediff when I run the query.
View 1 Replies
View Related
Apr 5, 2005
This is what i am tring to do. I generated system catalog with its poplution and created seperate .mdb file for
the meta database and now i need to insert to it using following funciton.:
"InsertSystemCatalogPopulation(db, metadb)''
i be happy if some one show me how i can
insert the meta data to created db. Thanks
Here is the code :
---------------------------
Private Sub Command0_Click()
CreateSystemCatalog
End Sub
Sub CreateSystemCatalog()
Dim metadb As Database, db As Database
Dim metadbname As String
Set db = CurrentDb()
metadbname = Left(db.NAME, Len(db.NAME) - 4) + "_meta.mdb"
Set metadb = DBEngine.Workspaces(0).CreateDatabase(metadbname, dbLangGeneral)
Call CreateSystemCatalogTables(metadb)
Call InsertSystemCatalogPopulation(db, metadb)
End Sub
Sub CreateSystemCatalogTables(metadb As Database)
metadb.Execute "CREATE TABLE SysTables( " & _
"tablename Char(30) NOT NULL, " & _
"PRIMARY KEY (tablename));"
metadb.Execute "CREATE Table SysColumns( " & _
"tablename Char(30) NOT NULL, " & _
"columnname Char(30) NOT NULL, " & _
"required Logical NOT NULL, " & _
"type Char(10) NOT NULL, " & _
"length Smallint, " & _
"PRIMARY KEY (tablename, columnname))"
metadb.Execute "CREATE TABLE SysKeys( " & _
"tablename Char(30) NOT NULL, " & _
"keyname Char(30) NOT NULL, " & _
"keytype Char(9) NOT NULL, " & _
"tablename_prim Char(30) NOT NULL, " & _
"PRIMARY KEY (tablename, keyname));"
metadb.Execute "CREATE TABLE SysKeyColumns( " & _
"tablename Char(30) NOT NULL, " & _
"keyname Char(30) NOT NULL, " & _
"keycolumn_seqno Char(30) NOT NULL, " & _
"columnname Char(30) NOT NULL, " & _
"PRIMARY KEY (tablename, keyname, columnname), " & _
"UNIQUE (tablename, keyname, keycolumn_seqno));"
metadb.Execute "ALTER TABLE SysColumns " & _
"ADD FOREIGN KEY (tablename) REFERENCES SysTables;"
metadb.Execute "ALTER TABLE SysKeys " & _
"ADD FOREIGN KEY (tablename) REFERENCES SysTables;"
metadb.Execute "ALTER TABLE SysKeys " & _
"ADD FOREIGN KEY (tablename_prim) REFERENCES SysTables;"
metadb.Execute "ALTER TABLE SysKeyColumns " & _
"ADD FOREIGN KEY (tablename, keyname) REFERENCES SysKeys;"
metadb.Execute "ALTER TABLE SysKeyColumns " & _
"ADD FOREIGN KEY (tablename, columnname) REFERENCES SysColumns;"
End Sub
-------------------
View 2 Replies
View Related
Dec 22, 2005
Hi, I am inserting records into a table where one of the fields I want to insert is a date value from a form. When I run the query without inserting it it works fine. When I change the value into some other type (a string) it also works fine. I have tried it with a date 05/05/05 so it has nothing to do with the sequence. The destination field as well as the form has identical data types. The keys are OK as the record is inserted. Only the date is missing.
The error message displayed states:
Microsoft access set 6 fields to NULL due to a type conversion failure ...
I guess I am using the 'dateInvoiceSent' wrong or without the required format command. Any help is most appreciated.
INSERT INTO clientInvoice (InvoiceNo,clientName,dateInvoiceSent)
SELECT DISTINCT jobInvoice.InvoiceNo, job.ClientName, [Forms]![CreateInvoices].[InvoiceDate] AS dateInvoiceSent
FROM job INNER JOIN jobInvoice ON job.JobRef = jobInvoice.JobRef
WHERE (((job.JobRef)=[jobinvoice].[jobref]));
View 1 Replies
View Related
Dec 22, 2005
Hi, I am inserting records into a table where one of the fields I want to insert is a date value from a form. When I run the query without inserting it it works fine. When I change the value into some other type (a string) it also works fine. I have tried it with a date 05/05/05 so it has nothing to do with the sequence. The destination field as well as the form has identical data types. The keys are OK as the record is inserted. Only the date is missing.
The error message displayed states:
Microsoft access set 6 fields to NULL due to a type conversion failure ...
I guess I am using the 'dateInvoiceSent' wrong or without the required format command. Any help is most appreciated.
INSERT INTO clientInvoice (InvoiceNo,clientName,dateInvoiceSent)
SELECT DISTINCT jobInvoice.InvoiceNo, job.ClientName, [Forms]![CreateInvoices].[InvoiceDate] AS dateInvoiceSent
FROM job INNER JOIN jobInvoice ON job.JobRef = jobInvoice.JobRef
WHERE (((job.JobRef)=[jobinvoice].[jobref]));
View 1 Replies
View Related
Feb 3, 2006
I have a form that has a date field. I have a button next to that field that opens another form that I've placed a calendar on. Is there a way to click on a date in the calendar and have it automatically insert the date into the date field on the previous form?
View 12 Replies
View Related
Aug 24, 2006
Help!
Im trying to get a text box on a form to always automatically insert the current date. This seems like it would be so easy to me but apparently im stooopid.
Thanks to anyone that can help.
( I already have the field in the table, and its in the query)
View 2 Replies
View Related
Aug 24, 2006
Help!
Im trying to get a text box on a form to always automatically insert the current date. This seems like it would be so easy to me but apparently im stooopid.
Thanks to anyone that can help.
( I already have the field in the table, and its in the query)
View 2 Replies
View Related
Sep 10, 2013
I have an insert into statement this involves inserting values into date/time fields. I understand the a '#' has to come before and after the value, and when all values are populated this works great.
Code:
INSERT INTO TableName VALUES (#1/1/2013#,#1/2/2013#)
However, what about in the case of Nulls? The resulting statement then looks like the following. This results in the statement trying to put a '##' in the table and it doesn't like it.
Code:
INSERT INTO TableName VALUES (#1/1/2013#,##)
I am trying to input records into a local Access table, not a SQL table.
View 1 Replies
View Related
May 17, 2005
I have an application that has a text box on a form that points to a memo data type.
On the form, I wish to allow the users to insert the system date with a short-cut key. (Trying to use Ctrl-D).
I have coded a Keypress Event that checks for the Ctrl-D. "Current" code is below:
===============================
Private Sub PM_Comments_KeyPress(KeyAscii As Integer)
Dim MyString As String
Dim MyDate As Variant
'Check to see if user pressed Ctrl-D
If KeyAscii = 4 Then
MyDate = Date
MyString = Format(MyDate, "Short Date") & ":"
SendKeys ("") ' Perhaps I need to dump the Ctrl-D in the buffer???
SendKeys (MyString)
End If
End Sub
==================================
I say current because I have tried MANY variations. The code is executing properly on the Ctrl-D. I have watched the variables in debug mode and they contain what I want, namely "mm/dd/yy:" for the current date. Problem is, it inserts the TIME in the memo field. And it is the system time, not the integer of the date formatted into the time.
Crazy thing is, in debug mode, the darn thing will do what I want and insert the date in the VB editor page. Just won't do it in the memo field on the form.
NCWalker
View 3 Replies
View Related
Dec 15, 2005
We have a memo field that we use for on going notes.
Each day they may type more notes.
Is there a shortcut key, a button, or a way to automatically enter the date
and time before the notes?
View 1 Replies
View Related
Oct 26, 2014
i have just started to use access and i know how to insert the current date into a field using date() but i am not sure will this change the date everytime i open the form ? i want to create a form for invoices that shows the date the invoice was created and doesnt change if i re open for editing,
Ads: Technewonline is a website that specializes in introducing the latest technologies such as Best Tablet Android Have Price Under $200 and Best tablet of Apple in 2014 and The Best Midrange Smart Phone In 2014 and Top Best Ultrabook Of 2014 and The Best Phones 4G Valued At Under 300 USD is also a website for sharing your tips about computers, mobile phones and tablets, products are available from leading supermarkets will surely satisfy you.
View 1 Replies
View Related
Aug 16, 2006
I have a form with two controls: DateFrom & DateToI want to filter based on those criteria but when the criteria is ommitted (NULL) I want to display all.In case of other controls I have an SQL as follows:... WHERE (tblClassesOffered.Course = Forms!frmClassReport.class Or Forms!frmClassReport.class Is Null)But with the dates controls the best I could come up with is:...Between IIf(IsNull([Forms]![frmClassReport].[DateFrom]),#1/1/1900#,[Forms]![frmClassReport].[DateFrom]) And IIf(IsNull([Forms]![frmClassReport].[DateTo]),#1/1/3000#,[Forms]![frmClassReport].[DateTo]) Or Is NullHowever that solution is kind of ugly becasue instead of wild cards (I couldn't make them work) I'm using those made up dates and I also wish not to use the IIF if possible (like the first statement).
View 2 Replies
View Related
Mar 9, 2005
good morning all,
I am using the afterupdate event on this forms field. If this date field is NOT EMPTY, then i want the next field to be visible. Here is the code i have
If Me.quotedate = ????????? Then
Me.quotehow.Visible = True
Else
Me.quotehow.Visible = False
End If
I have done this with text fields where i have a defined data to be = to, but for a date field what do i put in ?????????? to show is not null?
Thanks
Kevin
View 6 Replies
View Related
