Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

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
?
162 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
2 Comments
 
LVL 28

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 52

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

578 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