Go Back   Access World Forums > Microsoft Access Discussion > Forms

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 10-09-2019, 03:24 PM   #1
theinviter
Newly Registered User
 
Join Date: Aug 2014
Posts: 61
Thanks: 23
Thanked 1 Time in 1 Post
theinviter is on a distinguished road
Date with null

Hi Guys;

nee help

i have created a form and when i select from drop list the item name the exp date field will be updated based on the number with each drop list but if the number is null i got error " Type Mismatch",


the code is:
Private Sub Name_AfterUpdate()

BUD = name.Column(3)
exp_date = Date + BUD

End Sub

i wanna if the number is null then Exp. date will be only current date.

please advise me how to resolve this issue

theinviter is offline   Reply With Quote
Old 10-09-2019, 03:27 PM   #2
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,103
Thanks: 13
Thanked 4,075 Times in 4,010 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Date with null

Try

BUD = Nz(name.Column(3), 0)

Name is not a good field/control name as it is a reserved word. It can conflict with the Name property of an object.
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 10-09-2019, 03:34 PM   #3
theinviter
Newly Registered User
 
Join Date: Aug 2014
Posts: 61
Thanks: 23
Thanked 1 Time in 1 Post
theinviter is on a distinguished road
Re: Date with null

i changed to Item_Name but still same issue
i got this line highlighted yellow:
exp_date = Date + BUD

Error " Type Mismatch"

theinviter is offline   Reply With Quote
Old 10-09-2019, 03:35 PM   #4
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,103
Thanks: 13
Thanked 4,075 Times in 4,010 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Date with null

Did you try the Nz() function I suggested?
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 10-09-2019, 03:36 PM   #5
theinviter
Newly Registered User
 
Join Date: Aug 2014
Posts: 61
Thanks: 23
Thanked 1 Time in 1 Post
theinviter is on a distinguished road
Re: Date with null

yes i tried but the same issue
theinviter is offline   Reply With Quote
Old 10-09-2019, 03:38 PM   #6
theinviter
Newly Registered User
 
Join Date: Aug 2014
Posts: 61
Thanks: 23
Thanked 1 Time in 1 Post
theinviter is on a distinguished road
Re: Date with null

this is the code

Private Sub Drug_Name_AfterUpdate()
Order_Date = Now


BUD = Nz(Item_name.Column(3), 0)
exp_date = Date + BUD

End Sub
theinviter is offline   Reply With Quote
Old 10-09-2019, 03:39 PM   #7
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,103
Thanks: 13
Thanked 4,075 Times in 4,010 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Date with null

What is BUD? I assume a variable, but it isn't declared here. What exactly does that combo column contain? If it's text, you might need to convert it

BUD = CInt(Nz(Item_name.Column(3), 0))

Do you know how to set a breakpoint and step through the code, seeing what all the objects contain at runtime?

__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 10-09-2019, 03:46 PM   #8
theinviter
Newly Registered User
 
Join Date: Aug 2014
Posts: 61
Thanks: 23
Thanked 1 Time in 1 Post
theinviter is on a distinguished road
Re: Date with null

BUD its variable contain a number , once i select form drop list item name BUD will get update with number it represent .

please this modified code :
Private Sub item_Name_AfterUpdate()
Order_Date = Now

BUD = CInt(Nz(item_name.Column(3), 0))

exp_date = Date + BUD


End Sub

run time Error "13" type mismatch
theinviter is offline   Reply With Quote
Old 10-09-2019, 03:47 PM   #9
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,103
Thanks: 13
Thanked 4,075 Times in 4,010 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Date with null

On what line? Can you attach the db here?
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 10-09-2019, 03:53 PM   #10
theinviter
Newly Registered User
 
Join Date: Aug 2014
Posts: 61
Thanks: 23
Thanked 1 Time in 1 Post
theinviter is on a distinguished road
Re: Date with null

the error in this line

exp_date = Date + BUD

cant attached its big size.
Attached Images
File Type: png Untitled.png (16.0 KB, 8 views)
theinviter is offline   Reply With Quote
Old 10-09-2019, 03:53 PM   #11
theinviter
Newly Registered User
 
Join Date: Aug 2014
Posts: 61
Thanks: 23
Thanked 1 Time in 1 Post
theinviter is on a distinguished road
Re: Date with null

i changed the field to drug_name
theinviter is offline   Reply With Quote
Old 10-09-2019, 03:57 PM   #12
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,103
Thanks: 13
Thanked 4,075 Times in 4,010 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Date with null

From the image it appears the combo has a zero length string rather than Null. Perhaps something like

Code:
If Len(drug_name.Column(3) & vbNullString) > 0 Then
  BUD = CInt(drug_name.Column(3))
Else
  BUD = 0
End If
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
The Following User Says Thank You to pbaldy For This Useful Post:
theinviter (10-09-2019)
Old 10-09-2019, 03:59 PM   #13
theinviter
Newly Registered User
 
Join Date: Aug 2014
Posts: 61
Thanks: 23
Thanked 1 Time in 1 Post
theinviter is on a distinguished road
Re: Date with null

thank you so much
solve successfully
theinviter is offline   Reply With Quote
Old 10-09-2019, 04:01 PM   #14
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,103
Thanks: 13
Thanked 4,075 Times in 4,010 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Date with null

Happy to help! There's a one-line solution along the lines of:

BUD = IIf(Nz(drug_name.Column(3), "") = "", 0, CInt(drug_name.Column(3)))

__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
The Following User Says Thank You to pbaldy For This Useful Post:
theinviter (10-09-2019)
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Null Date Value and String vs Date and #Error BrokenBiker Queries 7 09-13-2015 06:54 PM
When date null return today's date ECL16 Queries 1 08-08-2013 11:01 AM
2 Table date fields that can be a date or null - determine if they are they equal Rx_ Modules & VBA 7 02-08-2013 11:31 AM
What is the best way to insert Date/Null into a Date Field? babakanoush General 1 12-12-2004 02:33 AM
Updating a Null Date to a Null Date wjoc1 Queries 4 09-03-2004 07:59 AM




All times are GMT -8. The time now is 10:54 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World