Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

A calculated field in Access returns time - But if the calculated field is blank it returns error

Posted on 2014-12-02
2
Medium Priority
?
159 Views
Last Modified: 2015-01-15
Hi Experts

I think this might be an easy question for Access Experts :-)

I have a calculated field that has to return the starttime for bowling Lanes. But in the same file, I have the transactions, related to other form of sales (where there is no starting time.

If there is a starting time, below function Works great. [Placement] is a textfield containing other information as well.

Starttime: TimeSerial(Left([Placement];2);Mid([placement];3;2);"00")

If there is no starttime, I want my Query to return a blank cell.

regards

Jørgen
0
Comment
Question by:Jorgen
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 27

Accepted Solution

by:
MacroShadow earned 900 total points
ID: 40477888
If when there is no start time Placement is blank, the following will work:
Starttime: IIF(Len(Placement)=0,"",(TimeSerial(Left([Placement];2);Mid([placement];3;2);"00"))

Open in new window

If it will contain other information, I'll have to see a sample of what it can contain.
0
 
LVL 51

Assisted Solution

by:Gustav Brock
Gustav Brock earned 600 total points
ID: 40478046
A blank cell is Null, thus:

Starttime: IIf(IsDate(Left([Placement];2) & ":" & Mid([Placement];3;2));TimeSerial(Left([Placement];2);Mid([Placement];3;2);0);Null)

/gustav
0

Featured Post

How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question