Go Back   Access World Forums > Microsoft Access Discussion > Forms

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 01-05-2018, 01:26 AM   #1
frankt68
Newly Registered User
 
Join Date: Mar 2012
Posts: 50
Thanks: 14
Thanked 0 Times in 0 Posts
frankt68 is on a distinguished road
If the field is empty, use a different field as a data source

I wonder if this is possible?

I have a tale named tCBZ that serves as a data source for the form named fCBZ. The table has several fields, including the Short_Name and Long_Name fields. On the fCBZ form, only the Short_Name field is used(because it is shorter). However, for some records the field is blank. In this case, that is, when the Short_Name field is empty, I would like to use the Long_Name field as a data source.
So I'd like to do something like
if Short_Name is blank then show Long_name else show Short_Name


Is it possible and what do I have to do?

frankt68 is offline   Reply With Quote
Old 01-05-2018, 01:31 AM   #2
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 6,432
Thanks: 165
Thanked 1,736 Times in 1,706 Posts
Minty is a jewel in the rough Minty is a jewel in the rough Minty is a jewel in the rough
Re: If the field is empty, use a different field as a data source

You can use an IIf() in your underlying query for the form something like;

Code:
NonBlankName: IIf(Len(Short_Name & "") > 0, Short_Name, Long_Name)
__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is online now   Reply With Quote
The Following User Says Thank You to Minty For This Useful Post:
frankt68 (01-05-2018)
Old 01-05-2018, 01:47 AM   #3
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 10,946
Thanks: 114
Thanked 2,993 Times in 2,721 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: If the field is empty, use a different field as a data source

Or another similar IIf solution:

Code:
IIf(Nz([Short_Name],"")<>"",[Short_Name],[Long_Name])

__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin (Mendip Data Systems)
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
The Following User Says Thank You to isladogs For This Useful Post:
frankt68 (01-05-2018)
Old 01-05-2018, 02:08 AM   #4
frankt68
Newly Registered User
 
Join Date: Mar 2012
Posts: 50
Thanks: 14
Thanked 0 Times in 0 Posts
frankt68 is on a distinguished road
Re: If the field is empty, use a different field as a data source

Thank you Minty and ridders, problem solved!

frankt68 is offline   Reply With Quote
Reply

Tags
data source , field

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
how to know if a field is empty or not in a data macro mana Access Web 2 10-12-2015 08:07 AM
Query help to pull data where table field is empty YNWA Queries 0 03-16-2010 06:29 AM
Warn user if a field is empty at data entry gear Forms 2 07-13-2008 10:15 PM
Data source field from form tikkat3 Reports 13 05-30-2007 10:37 AM
Replcate data from field down into empty entries octocom Tables 0 11-15-2005 11:13 AM




All times are GMT -8. The time now is 04:14 AM.


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